我们在日常的crud开发中,经常会遇到查询数据不是预期效果的问题,但是有感觉自己的查询语句没什么问题呀?怎么会出错呢? 今天我也中招了,就很烦,sql如下:
在查询语句中我们有一个条件是year=2021,但是查询结果还包含了2020的数据,这无疑是错误的
SELECT
sa.`year`,
ss.status_name,
ss2.status_name,
ss3.status_name
FROM
sr_task st
LEFT JOIN table1 sa ON sa.task_id = st.id
LEFT JOIN table2 ss ON ss.id = sa.submit_status_id
LEFT JOIN table2 ss2 ON ss2.id = sa.accept_status_id
LEFT JOIN table2 ss3 ON ss3.id = sa.current_status_id
LEFT JOIN table3 sp ON st.principal_id = sp.id
LEFT JOIN tables4 su ON st.assume_unit_id = su.id
WHERE
st.del_flag = 0
AND sa.del_flag = 0
AND sp.del_flag = 0
AND su.del_flag = 0
AND is_annals = 1
AND sa.YEAR = 2021
AND ss.id = 5
OR ss2.id = 5
OR ss3.id = 5
查询结果是:
之后百度查询后说是and的优先级比or的高,他是先选出了符合and条件的,然后,or操作选出了符合or里面条件的,取了并集
, 所以导致了查询结果和预期结果不符的情况
解决方法如下:
使用括号吧and之后的or条件括起来,就可以解决这个问题
SELECT
sa.`year`,
ss.status_name,
ss2.status_name,
ss3.status_name
FROM
sr_task st
LEFT JOIN table1 sa ON sa.task_id = st.id
LEFT JOIN table2 ss ON ss.id = sa.submit_status_id
LEFT JOIN table2 ss2 ON ss2.id = sa.accept_status_id
LEFT JOIN table2 ss3 ON ss3.id = sa.current_status_id
LEFT JOIN table3 sp ON st.principal_id = sp.id
LEFT JOIN tables4 su ON st.assume_unit_id = su.id
WHERE
st.del_flag = 0
AND sa.del_flag = 0
AND sp.del_flag = 0
AND su.del_flag = 0
AND is_annals = 1
AND sa.YEAR = 2021
AND (ss.id = 5
OR ss2.id = 5
OR ss3.id = 5)