结论:and的优先级高于or,可理解为or会把条件分为左右两边来查。
必要时,需要用()将需要是或者关系的字段括起来。
1、如下sql:
SELECT id,state,type
FROM employee
WHERE state = 2 AND type = 2 OR type = 3;
本来想查employee表状态为2的,同时,类型为2或者3的数据。
但从结果可以看出,此时查出了state = 2 AND type = 2的数据,和type=3的数据。这并不是我们想要的结果。
解决办法:将需要是或者关系的字段用()括起来。
SELECT id,state,type FROM employee
WHERE state = 2 AND (type = 2 OR type = 3);
2、查询状态为1的员工,同时,type = 2,或者type= 3并且sex=1,或者type = 1同时sex=2或者dept_id=1;
sql如下:
SELECT id,state,type,sex,dept_id
FROM employee
WHERE state = 1 AND (type = 2 OR type = 3 AND sex = 1 OR type = 1 AND (sex = 2 OR dept_id = 1));
结果:
因为and的优先级高于or,所以type = 3 AND sex = 1 的没用括号,后边的type = 1 AND (sex = 2 OR dept_id = 1)需要加括号。