MYSQL 支持EXCEPT语句吗? 例子里EXCEPT后面的换成NOT IN可以吗? 两个例子错在哪里啊?
关注:143 答案:2 mip版
解决时间 2021-01-17 23:20
提问者祇為焄菂兲哋閤
2021-01-17 04:38
mysql> select s.sname
-> from sailors s
-> where s.sid in(select r.sid
-> from reserves r
-> where r.bid in(select b.bid
-> from boats b
-> where b.color='red'))
-> except
-> select s2.sname
-> from sailors s2
-> where s2.sid in(select r2.sid
-> from reserves r2
-> where r2.bid in(select b2.bid
-> from boats b2
-> where b2.color='green'));
ERROR 1064 (42000): 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 'excep
t
select s2.sname
from sailors s2
where s2.sid in(select r2.sid
' at line 8
EXCEPT换成NOT IN
mysql> select s.sname
-> from sailors s
-> where s.sid in(select r.sid
-> from reserves r
-> where r.bid in(select b.bid
-> from boats b
-> where b.color='red'))
-> and not in(select r2.sid
-> from reserves r2
-> where r2.bid in(select b2.bid
-> from boats b2
-> where b2.color='green'));
ERROR 1064 (42000): 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 'in(se
lect r2.sid
from reserves r2
where r2.bid in(' at line 8
最佳答案
二级知识专家云朵有点甜
2021-01-17 05:10
你的代码好长啊, 我就简单解释一下
MySQL 不支持 EXCEPT, 但是可以有替代的办法。
-- 测试表,与测试数据
CREATE TABLE union_tab_1 (
id INT,
val VARCHAr(10)
);
CREATE TABLE union_tab_2 (
id INT,
val VARCHAr(10)
);
INSERT INTO union_tab_1 VALUES(1, 'A');
INSERT INTO union_tab_1 VALUES(2, 'B');
INSERT INTO union_tab_1 VALUES(3, 'C');
INSERT INTO union_tab_2 VALUES(1, 'A');
INSERT INTO union_tab_2 VALUES(1, 'A');
INSERT INTO union_tab_2 VALUES(2, 'B');
INSERT INTO union_tab_2 VALUES(4, 'D');
EXCEPT– 返回第一个表中有、第二个表中没有的数据
SQL Server 是支持的
1> SELECt * FROM union_tab_1
2> EXCEPT
3> SELECt * FROM union_tab_2;
4> go
id val
----------- ----------
3 C
(1 行受影响)
1> SELECt * FROM union_tab_2
2> EXCEPT
3> SELECt * FROM union_tab_1;
4> go
id val
----------- ----------
4 D
(1 行受影响)
MySQL 不支持
实现相同功能的 SQL 如下:
SELECt
DISTINCT union_tab_1.*
FROM
union_tab_1 LEFT JOIN union_tab_2
ON (union_tab_1.id = union_tab_2.id
AND union_tab_1.val = union_tab_2.val)
WHERe
union_tab_2.id IS NULL;
+------+------+
| id | val |
+------+------+
| 3 | C |
+------+------+
1 row in set (0.00 sec)
全部回答
1楼癡情菂尐豬豬
2021-01-17 06:00
应该不能吧。
我要举报
如果感觉以上信息为低俗/不良/侵权的信息,可以点下面链接进行举报,我们会做出相应处理,感谢你的支持!
点此我要举报以上信息!
推荐资讯
大家都在看