首先确定此表是否包含了s1全部的配件,若是则 select jno from spj where sno='s1' group by jno having count(pno)=(select count(distinct pno) from spj where sno='s1') 大概是这个样子,写的不好。见笑了
这个题好像<<数据系统概论>>上的哟:) 好像还有其它的表, 那样的话利用其它表就好做了!
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(*) 求记录数
to iainet() 确实是那上面的题,想了好久没有头绪,按照表,人工找了一下,发现应该是没有解的 其他的表没用的,这道题并没有供应商所供应的全部零件的表供应商S1提供的零件就有P1,P2大力的测试似乎也显示出没有解,不过他的答案我有点儿看不明白.group by后面加两个列名是什么意思啊??
select jno from spj where sno='s1' group by jno having count(pno)=(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(*) 求记录数
确实是那上面的题,想了好久没有头绪,按照表,人工找了一下,发现应该是没有解的
其他的表没用的,这道题并没有供应商所供应的全部零件的表供应商S1提供的零件就有P1,P2大力的测试似乎也显示出没有解,不过他的答案我有点儿看不明白.group by后面加两个列名是什么意思啊??
crycat(crycat)当然也是正确的~~~呵呵~~不好意思~~~