抱歉,我只是不知道如何简要解释我想要实现的目标。但其实很简单。
我有下表egr
:
+---------+------------+
| offid | groupid |
+---------+------------+
| 1 | 101 |
| 1 | 202 |
| 2 | 202 |
| 2 | 404 |
+---------+------------+
我想获取 groupid 未链接到其他 offid 的行。结果将是:
+---------+------------+
| offid | groupid |
+---------+------------+
| 1 | 101 |
| 2 | 404 |
+---------+------------+
这可行,但我想知道是否有更优雅的方法来做到这一点?
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);
Thanks