1.错误示例
SELECT
t.task_department_name,
COUNT(*) total_count,
SUM(CASE WHEN status='done' THEN 1 ELSE 0 END) AS finish_count,
SUM(CASE WHEN status<>'done' THEN 1 ELSE 0 END) AS unfinish_count
FROM `t_task` t
-- WHERE task_type = 'update' AND (task_department_name LIKE '%镇' OR task_department_name LIKE '%街')
WHERE t.task_department_name LIKE '%镇'
OR t.task_department_name LIKE '%街'
OR t.task_department_name LIKE '%机关'
AND t.task_type in ('create')
GROUP BY task_department_name
结果:该结果与预期不符(eg:秀全街总数应该是3个,并且没有完成的任务)
2。错误原因分析
AND优先级高于OR优先级,上面代码实际上下面这样的:
SELECT
t.task_department_name,
COUNT(*) total_count,
SUM(CASE WHEN status='done' THEN 1 ELSE 0 END) AS finish_count,
SUM(CASE WHEN status<>'done' THEN 1 ELSE 0 END) AS unfinish_count
FROM `t_task` t
-- WHERE task_type = 'update' AND (task_department_name LIKE '%镇' OR task_department_name LIKE '%街')
WHERE t.task_department_name LIKE '%镇'
OR t.task_department_name LIKE '%街'
OR (t.task_department_name LIKE '%机关'
AND t.task_type in ('create'))
GROUP BY task_department_name
也就是说:t.task_department_name LIKE '%机关'先和t.task_type in ('create')进行AND运算,这就是出错之处
3.错误解决:加括号明确优先级:
SELECT
t.task_department_name,
COUNT(*) total_count,
SUM(CASE WHEN status='done' THEN 1 ELSE 0 END) AS finish_count,
SUM(CASE WHEN status<>'done' THEN 1 ELSE 0 END) AS unfinish_count
FROM `t_task` t
-- WHERE task_type = 'update' AND (task_department_name LIKE '%镇' OR task_department_name LIKE '%街')
WHERE (t.task_department_name LIKE '%镇'
OR t.task_department_name LIKE '%街'
OR t.task_department_name LIKE '%机关')
AND t.task_type in ('create')
GROUP BY task_department_name
解决: