来自同一个表的2个SQL查询之间的差异(Difference between 2 SQL queries from same table)
表SECURITYGROUPSID:
GROUPNAME | SIDNAME -------------------------- Group 1 Apple Group 1 Apples Group 1 Applesauce Group 1 Applesauces Group 1 Appleton Group 2 Apple Group 2 Applesauce Group 2 Appleton对于像'Apple%'这样的值,我需要第1组和第2组之间SIDNAME值的差异。 例如,如果我执行以下2个查询,我需要在底部的结果查询。
SELECT SIDNAME FROM SECURITYGROUPSID WHERE GROUPNAME = 'Group 1' AND SIDNAME LIKE 'Apple%'; SELECT SIDNAME FROM SECURITYGROUPSID WHERE GROUPNAME = 'Group 2' AND SIDNAME LIKE 'Apple%';结果查询应该是:Apples Applesauces
Table SECURITYGROUPSID:
GROUPNAME | SIDNAME -------------------------- Group 1 Apple Group 1 Apples Group 1 Applesauce Group 1 Applesauces Group 1 Appleton Group 2 Apple Group 2 Applesauce Group 2 AppletonI need the difference in SIDNAME values between Groups 1 and 2 for values like 'Apple%'. For example, if I perform the following 2 queries, I need the result query at the bottom.
SELECT SIDNAME FROM SECURITYGROUPSID WHERE GROUPNAME = 'Group 1' AND SIDNAME LIKE 'Apple%'; SELECT SIDNAME FROM SECURITYGROUPSID WHERE GROUPNAME = 'Group 2' AND SIDNAME LIKE 'Apple%';The result query should be: Apples Applesauces
最满意答案
使用not exists()
select o.sidname from securitygroupsid o where o.groupname = 'Group 1' and o.sidname like 'Apple%' and not exists ( select 1 from securitygroupsid i where i.groupname = 'Group 2' and i.sidname = o.sidname )或者not in()使用not in()
select o.sidname from securitygroupsid o where o.groupname = 'Group 1' and o.sidname like 'Apple%' and o.sidname not in ( select i.sidname from securitygroupsid i where i.groupname = 'Group 2' and i.sidname like 'Apple%' )或者left join
select o.sidname from securitygroupsid o left join securitygroupsid i on o.sidname = i.sidname and o.groupname = 'Group 1' and i.groupname = 'Group 2' where o.sidname like 'Apple%' and i.sidname is nullusing not exists()
select o.sidname from securitygroupsid o where o.groupname = 'Group 1' and o.sidname like 'Apple%' and not exists ( select 1 from securitygroupsid i where i.groupname = 'Group 2' and i.sidname = o.sidname )or using not in()
select o.sidname from securitygroupsid o where o.groupname = 'Group 1' and o.sidname like 'Apple%' and o.sidname not in ( select i.sidname from securitygroupsid i where i.groupname = 'Group 2' and i.sidname like 'Apple%' )or left join
select o.sidname from securitygroupsid o left join securitygroupsid i on o.sidname = i.sidname and o.groupname = 'Group 1' and i.groupname = 'Group 2' where o.sidname like 'Apple%' and i.sidname is null更多推荐
发布评论