我有这样的数据假人
CREATE TABLE A ( users_id INT);
INSERT INTO A VALUES (1), (2), (3), (4);
CREATE TABLE B (users_id INT);
INSERT INTO B VALUES (2), (3), (4), (5);
CREATE TABLE C (users_id INT);
INSERT INTO C VALUES (3), (4), (5), (6);
CREATE TABLE D (users_id INT);
INSERT INTO D VALUES (4), (5), (6), (7);
我想选择在表D和B上存在但在表A上不存在的users_id
我试过这个查询
SELECT DISTINCT b1.users_Id FROM b b1
WHERE EXISTS (SELECT d1.users_Id FROM D d1
WHERE b1.users_id = d1.users_id)
AND NO EXISTS (SELECT a1.users_id from A a1
WHERE a1.users_id = d1.users_id
and a1.users_id = b1.users_id
and b1.users_id = d1.users_id);
但是mysql说
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT a1.users_id from A a1 WHERE a1.users_id = d' at line 4
相同查询的替代形式：
SELECT DISTINCT b.users_Id
FROM b
JOIN d
ON b.users_id = d.users_id
LEFT JOIN a
ON b.users_id = a.users_id
WHERE a.users_id IS NULL
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句