mysql - 为什么 OR in where 条件在 mysql 上不起作用

Question

为什么 OR in where 条件在 mysql 上不起作用

这是查询:

select CONCAT(D.did, ',', 'tbl_doctors') AS ID ,D.name,(S.name) As spname, D.addr 
FROM medicaldoctor.tbl_doctors D 
LEFT JOIN medicaldoctor.tbl_my_special M ON D.did = M.did 
LEFT JOIN medicaladmin.tbl_specialization S ON M.sid = S.id 
WHERE (D.addr LIKE '%Pune%' AND D.addr LIKE '%kothrud%') 
AND (S.name LIKE "%a%" OR D.name LIKE "%a%") 
group by D.addr

Answer 1

您的查询按编码工作。

    DROP TABLE IF EXISTS D,M,S;
    CREATE TABLE D(DID INT, NAME VARCHAR(20), ADDR VARCHAR(20));
    CREATE TABLE M(DID INT, SID INT);
    CREATE TABLE S(ID INT, NAME VARCHAR(20));

    INSERT INTO D VALUES(1,'AAA',',pUNE,KOTHRUD'),(2,'BAB',',pune,KOTHRUD'),(3,'CCC',',PUNE,,KOTHRUD');
    INSERT INTO M VALUES(1,1),(1,2),(2,1);
    INSERT INTO S VALUES(1,'XXX'),(2,'YYY');

    select CONCAT(D.did, ',', 'tbl_doctors') AS ID ,D.name,(S.name) As spname, D.addr 
    FROM  D 
    LEFT JOIN  M ON D.did = M.did 
    LEFT JOIN  S ON M.sid = S.id 
    WHERE (D.addr LIKE '%Pune%' AND D.addr LIKE '%kothrud%') 
    AND (S.name LIKE "%a%" OR D.name LIKE "%a%") 
    group by D.addr

+---------------+------+--------+---------------+
| ID            | name | spname | addr          |
+---------------+------+--------+---------------+
| 1,tbl_doctors | AAA  | XXX    | ,pUNE,KOTHRUD |
+---------------+------+--------+---------------+
1 row in set (0.00 sec)

分组者可以自由选择 2 名医生中的任何一个以及 1 名医生的 2 个专业中的任何一个，这是预期和记录的行为 https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html - 如果 ONLY_FULL_GROUP_BY 被禁用，则 GROUP BY 标准 SQL 使用的 MySQL 扩展允许选择列表、HAVING 条件或 ORDER BY 列表引用非聚合列，即使这些列在功能上不依赖于 GROUP BY 列。这会导致 MySQL 接受前面的查询。在这种情况下，服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的，这可能不是您想要的。