来自同一个表的2个SQL查询之间的差异(Difference between 2 SQL queries from same table)

来自同一个表的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 Appleton

I 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 null

using 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