一个SQL查询的问题(在线等)

[nickcheng]

求至少用了供应商S1所供应的全部零件的工程号JNO

SNO:供应商代码
PNO:零件代码
JNO:工程项目代码

spj表
-------------
SNO PNO JNO
s1 p1 j1
s1 p1 j3
s1 p1 j4
s1 p2 j2
s2 p3 j1
s2 p3 j2
s2 p3 j4
s2 p3 j5
s2 p5 j1
s2 p5 j2
s3 p1 j1
s3 p3 j1
s4 p5 j1
s4 p6 j3
s4 p6 j4
s5 p2 j4
s5 p3 j1
s5 p6 j2
s5 p6 j4
-------------

[helodd]

select JNO from spj where Sno='s1'

不是很明白你的意思，不知上面是不是你想要的

[pengdali]

select jno from spj where pno in (select pno from spj where sno='s1') group by pno,jno having sum(1)=(select count(distinct pno) from spj where sno='s1')



--测试：
create table #spj(SNO varchar(10),PNO varchar(10),JNO varchar(10))
insert #spj values('s1','p1','j1')
insert #spj values('s1','p1','j3')
insert #spj values('s1','p1','j4')
insert #spj values('s1','p2','j2')
insert #spj values('s2','p3','j1')
insert #spj values('s2','p3','j2')
insert #spj values('s2','p3','j4')
insert #spj values('s2','p3','j5')
insert #spj values('s2','p5','j1')
insert #spj values('s2','p5','j2')
insert #spj values('s3','p1','j1')
insert #spj values('s3','p3','j1')
insert #spj values('s4','p5','j1')
insert #spj values('s4','p6','j3')
insert #spj values('s4','p6','j4')
insert #spj values('s5','p2','j4')
insert #spj values('s5','p3','j1')
insert #spj values('s5','p6','j2')
insert #spj values('s5','p6','j4')


select jno from #spj where pno in (select pno from #spj where sno='s1') group by pno,jno having sum(1)=(select count(distinct pno) from #spj where sno='s1')


go
drop table #spj

--sum(1)等于：count(*) 求记录数