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

nickcheng 2003-05-12 01:04:56
求至少用了供应商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
-------------
...全文
3 点赞 收藏 2
写回复
2 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
pengdali 2003-05-13
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(*) 求记录数
回复
helodd 2003-05-13
select JNO from spj where Sno='s1'

不是很明白你的意思,不知上面是不是你想要的
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2003-05-12 01:04
社区公告
暂无公告