Sybase查询自联接

Question

下面的表存储ID和每个ID工作的部门。(一个ID可以适用于多个部门。我想找到只适用于一个部门的ID。

create table tempdb..dept (ID int, Dept varchar(10))
go
insert into tempdb..dept (ID, Dept) values (1, "HR")
insert into tempdb..dept (ID, Dept) values (2, "HR")
insert into tempdb..dept (ID, Dept) values (3, "HR")
insert into tempdb..dept (ID, Dept) values (4, "HR")
insert into tempdb..dept (ID, Dept) values (1, "Finance")
insert into tempdb..dept (ID, Dept) values (5, "Finance")
insert into tempdb..dept (ID, Dept) values (6, "Finance")
insert into tempdb..dept (ID, Dept) values (4, "Finance")
insert into tempdb..dept (ID, Dept) values (1, "IT")
insert into tempdb..dept (ID, Dept) values (7, "IT")
insert into tempdb..dept (ID, Dept) values (8, "IT")
insert into tempdb..dept (ID, Dept) values (4, "IT")

去

期望输出：

ID   Dept
2    HR
3    HR
5    Finance
6    Finance
7    IT
7    IT

Answer 1

您可以尝试HAVING计数从表中获取不重复的ID，然后使用自加入

SELECT t2.*
FROM (
SELECT ID
FROM tempdb..dept
GROUP BY ID
HAVING COUNT(*) = 1
) t1 INNER JOIN tempdb..dept t2
ON t1.ID = t2.ID

另一种方法可以尝试使用相关子查询

SELECT t2.*
FROM tempdb..dept t2
ON t1.ID = t2.ID
WHERE EXISTS (
SELECT 1
FROM tempdb..dept tt
WHERE t2.ID = tt.ID
GROUP BY tt.ID
HAVING COUNT(*) = 1
)