来自同一个表的2个SQL查询之间的差异

Table SECURITYGROUPSID:

表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.

对于像'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%';

The result query should be: Apples Applesauces

结果查询应该是：Apples Applesauces

2 个解决方案

#1

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

#2

Just add an except (SqlServer) between your queries. (Or MINUS for Oracle)

只需在查询之间添加except（SqlServer）即可。（或者MINUS for Oracle）

SELECT SIDNAME FROM SECURITYGROUPSID WHERE GROUPNAME = 'Group 1' AND SIDNAME LIKE 'Apple%';
EXCEPT
SELECT SIDNAME FROM SECURITYGROUPSID WHERE GROUPNAME = 'Group 2' AND SIDNAME LIKE 'Apple%';

#1