SQL查询以获取其列不应与其他表列匹配的表行。
SQL查询以获取其列不应与其他表列匹配的表行。
提问于 2016-11-07 12:21:43
提前谢谢你，
实际上我有两张桌子，两辆车和支票。Carts表包含以下行
id |username |orderid | exam_name | price
1 | Rajesh | ABC123 | PMP | $60
2 | Rajesh | ABC123 | CPM | $70
3 | David | ABC789 | ITIL | $80checks表包含如下行
id |username |order_id | exam | price
1 Rajesh | ABC123 | PMP | $60
2 Rajesh | ABC123 | CPM | $70我需要一个carts表的行数据，其orderid列和exam_name列不应该与checks、order_id列和need列匹配。
如下所示：
id |username |orderid | exam_name | price
1 | David | ABC789 | ITIL | $80回答 4
Stack Overflow用户
回答已采纳
发布于 2016-11-07 12:23:31
一种方法是not exists
select c.*
from carts c
where not exists (select 1
from checks ch
where ch.orderid = c.orderid and ch.exam_name = c.exam_name
);一个类似的方法是left join
select c.*
from carts c left join
checks ch
on ch.orderid = c.orderid and ch.exam_name = c.exam_name
where ch.orderid is null;在某些数据库中，您可以使用not in
select c.*
from carts c
where (c.orderid, c.exam_name) not in (select ch.orderid, ch.exam_name from checks ch);Stack Overflow用户
发布于 2016-11-07 12:24:09
select c.*
from carts c
left join checks ch on c.id = ch.id
where ch.id is null;希望这能解决你的问题。
Stack Overflow用户
发布于 2016-11-07 12:34:01
SELECT *
from Carts
where exam_name not in (select exam from checks)
and id not in (select id from checks)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40465076
复制相关文章
相似问题
腾讯云开发者
