NOT EXIST和NOT IN 和MINUS的用法

最新推荐文章于 2022-07-14 17:01:56 发布

weixin_30670965

最新推荐文章于 2022-07-14 17:01:56 发布

阅读量226

点赞数

原文链接：http://www.cnblogs.com/yangjinprocess/p/10142908.html

版权

MINUS

SELECT count(id) FROM householdstaffs s WHERE s.idcardno in(
　　SELECT h.idcardno FROM householdstaffs h WHERE h.orginternalcode like '1.1.9.9.%'
　　Minus
　　SELECT hs.idcardno FROM householdstaffs@spt_db hs WHERE hs.orginternalcode like '1.1.9.9.%')
AND s.orginternalcode like '1.1.9.9.%';

NOT IN

SELECT count(id) FROM householdstaffs h
WHERE h.idcardno NOT IN ( SELECT idcardno FROM householdstaffs@spt_db hs WHERE hs.orginternalcode like '1.1.9.9.%' )
AND h.orginternalcode like '1.1.9.9.%';

NOT EXIST

SELECT count(id) FROM householdstaffs h
WHERE
NOT EXISTS (
　　SELECT idcardno
　　FROM householdstaffs@spt_db hs
　　WHERE hs.idcardno = h.idcardno AND hs.orginternalcode like '1.1.9.9.%')
AND h.orginternalcode like '1.1.9.9.%';

转载于:https://www.cnblogs.com/yangjinprocess/p/10142908.html