有这样的数据，有若干小组，人员可以同时参与多个小组。
需要取得所有小组中都存在的人员。
组 人
A 1
A 2
A 3
B 2
B 3
B 4
B 5
C 1
C 2
C 4
D 2
最终结果应该是取得人员为2的结果。
create table t1(
group varchar(20),
usr varchar(20))
go
insert into t1 values('A','1')
insert into t1 values('A','2')
insert into t1 values('A','3')
insert into t1 values('B','2')
insert into t1 values('B','3')
insert into t1 values('B','4')
insert into t1 values('B','5')
insert into t1 values('C','1')
insert into t1 values('C','2')
insert into t1 values('C','4')
insert into t1 values('D','2')
------解决思路----------------------
;WITH t2 AS (
SELECT usr,
COUNT(DISTINCT [group]) gcount
FROM t1
GROUP BY usr
)
,t3 AS (
SELECT COUNT(DISTINCT [group]) gcount
FROM t1
)
SELECT t2.usr
FROM t2, t3
WHERE t2.gcount = t3.gcount
usr
--------------------
2
------解决思路----------------------
SELECT usr
FROM t1
GROUP BY usr
HAVING COUNT([group])=(SELECT COUNT(DISTINCT [group])FROM t1)