学步园

/*
一个查询包含的sql 语句
条件：任意一个id，例：id=1
需求：得到name值包含条件id的name的id 例：既包含a又包含b的id
思路：一开始想着用 join 或者 exists 来解决,费劲不少,解决了
	  但是问题是id不固定，name多少不固定..........
	  如果是id=n又有a,b又有c,d,e... 要写5个6个n个join还是exists.......
*/
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[name] varchar(1))
insert [tb]
select 1,'a' union all
select 1,'b' union all
select 2,'a' union all
select 2,'c' union all
select 3,'a' union all
select 3,'b' union all
select 3,'c' union all
select 4,'a' union all
select 4,'b' union all
select 4,'d' union all
select 5,'a' union all
select 5,'b' union all
select 5,'c' union all
select 5,'d' 
go

select [id] from [tb] where [name] in (select [name] from [tb] where [id]=1)
group by [id]
having count(distinct [name])>=(select count(1) from [tb] where [id]=1)