请问SQL语句如何写

[a1n1]

ID GroupID KeyID
1 6 20
2 6 21
3 6 22
4 7 29
5 7 30
6 8 20
7 8 21
8 8 29
9 10 20

请问如何找出 KEYID 既有20，又有21的groupID，即结果应该是
GroupID
6
8

SQL语句如何写

[我腫了]

USE test
GO


-->生成表tb

if object_id('tb') is not null 
	drop table tb
Go
Create table tb([ID] smallint,[GroupID] smallint,[KeyID] smallint)
Insert into tb
Select 1,6,20
Union all Select 2,6,21
Union all Select 3,6,22
Union all Select 4,7,29
Union all Select 5,7,30
Union all Select 6,8,20
Union all Select 7,8,21
Union all Select 8,8,29
Union all Select 9,10,20


----1. 
SELECT DISTINCT 
		GroupID 
	FROM tb AS a
	WHERE EXISTS(SELECT 1 FROM tb AS x
					WHERE x.GroupID=a.GroupID
						AND x.KeyID=20
			)
		AND EXISTS(SELECT 1 FROM tb AS x
					WHERE x.GroupID=a.GroupID
						AND x.KeyID=21
			)
/*
GroupID
-------
6
8
*/			

----2. 
SELECT DISTINCT 
		GroupID 
	FROM tb AS a
	WHERE GroupID IN(
					SELECT GroupID FROM tb
						WHERE KeyID=20
					INTERSECT
					SELECT GroupID FROM tb
						WHERE KeyID=21
				)
/*
GroupID
-------
6
8
*/		

[-Tracy-McGrady-]

select groupID from tb where KEYID=20 intersect select groupID from tb where KEYID=21

[a1n1]

哦不好意思，忘说了，20和21是从另一个表查询出来的数据，是一个结果集，不可分开 KeyID 20 21 而且个数可能也不定，也许是29,30,31这样的一个结果集

[我腫了]

把t2換成你的子查詢。

USE test
GO


-->生成表t1

if object_id('t1') is not null 
	drop table t1
Go
Create table t1([ID] smallint,[GroupID] smallint,[KeyID] smallint)
Insert into t1
Select 1,6,20
Union all Select 2,6,21
Union all Select 3,6,22
Union all Select 4,7,29
Union all Select 5,7,30
Union all Select 6,8,20
Union all Select 7,8,21
Union all Select 8,8,29
Union all Select 9,10,20
Union all Select 10,6,29	-- test


-->生成表t2

if object_id('t2') is not null 
	drop table t2
Go
Create table t2([keyID] smallint)
Insert into t2
Select 20
Union all Select 21
Union all Select 29


SELECT 
		GroupID
	FROM t1 AS a
		INNER JOIN t2 AS b ON a.KeyID=b.keyID
	GROUP BY 
		GroupID
	HAVING COUNT(1)=(SELECT COUNT(1) FROM t2)

/*
GroupID
-------
6
8
*/

[-Tracy-McGrady-]

select KeyID into #tb from 你那个表 -- declare @bl int declare @sql varchar(4000) if exists(select top(1)1 from #tb) begin select @bl=KeyID from #tb set @sql=@sql+' select groupID from tb where KEYID='+@bl+'intersect' delete #tb where KeyID=@bl end set @sql=substring(@sql,1,len(9)) exec(@sql) 没测试过，大概的思路就这样，你自己改改吧

[a1n1]

太感谢4楼 太牛了，请问这样做原理是什么

[我腫了]

keyid 匹配數量等於你要匹配的數量。

[a1n1]

好的，给分！