select jno from (select jno, SNO, count (*) c from spj group by JNO) a, (select count (*) c from (select distinct PNO from spj where sno = 's1' ) b where a.SNO = b.SNO
select jno from (select jno, SNO, count (*) c from spj group by JNO) a where a.c = (select count (*) c from (select distinct PNO from spj where sno = 's1' ))
select jno from spj where sno='s1' group by jno having count(distinct pno)= (select count (distinct PNO) from spj where sno = 's1')
只要某个项目 使用供应商s1提供的零件的种类数 与供应商s1提供的零件种类数相同就ok
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(*) 求记录数
什么意思啊,
(select jno, SNO, count (*) c from spj group by JNO) a,
(select count (*) c from (select distinct PNO
from spj
where sno = 's1'
) b
where a.SNO = b.SNO
(select jno, SNO, count (*) c from spj group by JNO) a
where a.c =
(select count (*) c from (select distinct PNO
from spj
where sno = 's1'
))
from spj
where sno='s1'
group by jno
having count(distinct pno)=
(select count (distinct PNO) from spj where sno = 's1')
使用供应商s1提供的零件的种类数
与供应商s1提供的零件种类数相同就ok
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(*) 求记录数