查询出一个表比除此而外一个表多出的记录

查询出一个表比另外一个表多出的记录

SQL code

with t1 as(select '1' id ,'test1' name, '1' codes from dualunion allselect '1' id ,'test1' name, '11' codes from dualunion allselect '2' id ,'test2' name, '2' codes from dualunion allselect '2' id ,'test2' name, '22' codes from dualunion allselect '3' id ,'test3' name, '3' codes from dual),t2 as(select '1' id ,'test1' name, '11' codes from dualunion allselect '1' id ,'test1' name, '111' codes from dualunion allselect '2' id ,'test2' name, '2' codes from dual)

有表t1,t2，如上；
现在要查询出，t1.id=t2.id and t1.name= t2.name 且t1中codes在t2中不存在的记录；
上面的结果应该是这样的：
id name codes
1 test1 1
2 test2 22

[解决办法]
既然关系也清楚 条件也清楚 直接写出来就可以查询到了
[解决办法]

SQL code

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE id=t1.id AND NAME=t1.NAME )AND codes NOT IN (SELECT DISTINCT codes FROM t2)或者SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE id=t1.id AND NAME=t1.NAME )MINUS SELECT * FROM t2
[解决办法]
SQL code
select * from t1    where not exists (select 1 from t2        where t1.id=t2.id and t1.name= t2.name and t1.codes=t2.codes)    and exists(select 1 from t2        where t1.id=t2.id and t1.name= t2.name);
[解决办法]
minus试试看 

 

查看更多 下一篇