求sql语句

[kevn]

表结构，id（主键），e_id（员工id），s_id(服务项目ID)
表数据如下：
id e_Id s_id
1 001 01
2 001 02
3 001 08
4 002 01
5 002 02
6 003 02
7 003 06
我想要的结果是每个员工都有这个服务项目ID时候显示的数据，也就是上述中，001有02，002有02，003有02，那我只想显示出一条,即 :
s_id
02

[SQL77]

SELECT S_ID FROM TB 
GROUP BY S_ID 
HAVING COUNT(1)=(SELECT COUNT(DISTINCT e_id) FROM TB)

[rucypli]

select s_id from tb group by s_id having count(distinct e_id)=(select distinct(e_id) from tb)

[百年树人]

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[e_Id] varchar(3),[s_id] varchar(2))
insert [tb]
select 1,'001','01' union all
select 2,'001','02' union all
select 3,'001','08' union all
select 4,'002','01' union all
select 5,'002','02' union all
select 6,'003','02' union all
select 7,'003','06'
 
---查询---
select s_id
from tb
group by s_id
having count(distinct e_id)=(select count(distinct e_id) from tb)

---结果---
s_id 
---- 
02

（所影响的行数为 1 行）

[Mr_Nice]

SELECT S_ID FROM TB 
GROUP BY S_ID 
HAVING COUNT(1)=(SELECT COUNT(DISTINCT e_id) FROM TB)

参考哈！

[dawugui]

select s_id from tb group by s_id having count(1) = (select count(distinct e_id))

[SQL77]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[e_Id] varchar(3),[s_id] varchar(2))
insert [tb]
select 1,'001','01' union all
select 2,'001','02' union all
select 3,'001','08' union all
select 4,'002','01' union all
select 5,'002','02' union all
select 6,'003','02' union all
select 7,'003','06'

SELECT S_ID FROM TB 
GROUP BY S_ID 
HAVING COUNT(1)=(SELECT COUNT(DISTINCT e_id) FROM TB)


（所影响的行数为 7 行）

S_ID 
---- 
02

（所影响的行数为 1 行）

[dawugui]

create table tb(id int,   e_Id varchar(10),   s_id varchar(10))
insert into tb values(1 ,     '001' ,     '01') 
insert into tb values(2 ,     '001' ,     '02') 
insert into tb values(3 ,     '001' ,     '08') 
insert into tb values(4 ,     '002' ,     '01') 
insert into tb values(5 ,     '002' ,     '02') 
insert into tb values(6 ,     '003' ,     '02') 
insert into tb values(7 ,     '003' ,     '06')
go

select s_id from tb group by s_id having count(1) = (select count(distinct e_id) from tb)

drop table tb 

/*
s_id       
---------- 
02

（所影响的行数为 1 行）
*/

[华夏小卒]

---------------------------------------------
--> Author : js_szy							
--> Target : ★★★						
--> Date   : 2010-01-05 10:06:07
--> Version: SQL Server 2005
---------------------------------------------
											    
--> 测试数据: @tb
declare @tb table (id int,e_Id varchar(3),s_id varchar(2))
insert into @tb
select 1,'001','01' union all
select 2,'001','02' union all
select 3,'001','08' union all
select 4,'002','01' union all
select 5,'002','02' union all
select 6,'003','02' union all
select 7,'003','06'

select s_id from @tb t
group by s_id
having(count(*)=(select count(distinct e_id) from @tb)) 

s_id
----
02

(1 行受影响)

[寒勿语]

7楼的
create table tb(id int, e_Id varchar(10), s_id varchar(10))
insert into tb values(1 , '001' , '01')
insert into tb values(2 , '001' , '02')
insert into tb values(3 , '001' , '08')
insert into tb values(4 , '002' , '01')
insert into tb values(5 , '002' , '02')
insert into tb values(6 , '003' , '02')
insert into tb values(7 , '003' , '06')
go

select s_id from tb group by s_id having count(1) = (select count(distinct e_id) from tb)

drop table tb

方法可以实现

[lkz99999]

同意一楼