工作记录_mysql_AND优先级高于OR优先级

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