(一张表)在没有联合的情况下获取不在的行 [英] (One table) get rows not in without union

问题描述

抱歉标题我只是不知道如何简要解释我想要实现的目标.但其实很简单.

Sorry for the title I just do not know how to explain what I want to achieve briefly. But it is actually simple.

我有下表egr:

+---------+------------+
|  offid  |  groupid   |
+---------+------------+
|       1 | 101        |
|       1 | 202        |
|       2 | 202        |
|       2 | 404        |
+---------+------------+

我想获取 groupid 未链接到另一个 offid 的行.结果将是:

I would like to get the rows for which a groupid is not linked to the other offid. Result would be:

+---------+------------+
|  offid  |  groupid   |
+---------+------------+
|       1 | 101        |
|       2 | 404        |
+---------+------------+

这可行，但我想知道是否有更优雅的方法来做到这一点?

This works but I was wondering if there was a more elegant way to do it?

select * from egr as egr1
where egr1.offid = 1
and egr1.groupid not in (select groupid from egr as egr2 where egr2.offid = 2 and egr1.groupid = egr2.groupid)
union
select * from egr as egr1
where egr1.offid = 2
and egr1.groupid not in (select groupid from egr as egr2 where egr2.offid = 1 and egr1.groupid = egr2.groupid)

如果您想尝试:

create table egr (offid int, groupid int);
insert into egr values (1, 101), (1, 202), (2, 202), (2, 404);

谢谢

推荐答案

这是您想要的吗?

select e.*
from egr e
where not exists (select 1
                  from egr e2
                  where e2.groupid = e.groupid and e2.offid <> e.offid 
                 );

或者，如果您想仅限于这两个优惠:

Or if you want to limit to just those two offers:

select e.*
from egr e
where e.offid in (1, 2) and
      not exists (select 1
                  from egr e2
                  where e2.groupid = e.groupid and 
                        e2.offid in (1, 2) and
                        e2.offid <> e.offid 
                 );

这篇关于(一张表)在没有联合的情况下获取不在的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！