sql 两张表的相同字段中刷选出不同数据

最新推荐文章于 2022-07-29 17:49:56 发布

xinyuezitang

最新推荐文章于 2022-07-29 17:49:56 发布

阅读量4.1k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Mysql / Mybatis 相关文章标签： sql 查询两张表相同字段的相同数据和不同数据

本文链接：https://blog.csdn.net/xinyuezitang/article/details/90297796

Mysql / Mybatis 相关专栏收录该内容

16 篇文章

订阅专栏

我们在业务逻辑中,经常会涉及两张表之间的数据比较,
其中涉及到某张表中独一份的数据,如下

1 两张表中相同字段name , 查询出在表a存在而表b中不存在的数据,并去重

方法一: 
SELECT DISTINCT(a.name) FROM table a
WHERE a.name NOT IN (SELECT DISTINCT(b.name) FROM table b)

方法二:
SELECT DISTINCT(a.name) FROM table a
WHERE NOT EXISTS (SELECT b.name FROM table b WHERE a.name = b.name)

方法三:
SELECT DISTINCT(a.name) FROM table a
LEFT JOIN table b ON a.name = b.name
WHERE b.name IS NULL

方法四:
SELECT DISTINCT(a.name) FROM table a
WHERE (SELECT COUNT(1) as num from FROM table b WHERE a.name = b.name) = 0

2 查询出字段name , 在表a存在而表b中不存在的数据的数量是多少

方法一: 
SELECT COUNT(DISTINCT(a.name)) FROM table a
WHERE a.name NOT IN (SELECT DISTINCT(b.name) FROM table b)

方法二:
SELECT COUNT(DISTINCT(a.name)) FROM table a
WHERE NOT EXISTS (SELECT b.name FROM table b WHERE a.name = b.name)

方法三:
SELECT COUNT(DISTINCT(a.name)) FROM table a
LEFT JOINtable b ON a.name = b.name
WHERE b.name IS NULL

方法四:
SELECT COUNT(DISTINCT(a.name)) FROM table a
WHERE (SELECT COUNT(1) as num from FROM table b WHERE a.name = b.name) = 0