select sno from spj where sno='s1' group by sno having count(distinct pno) = 零件数量
select jno from spj where sno='s1' group by jno having count(distinct pno)=( select count(distinct pno) from spj where sno='s1' )
select a.jno from (select sno,jno,[ssum]=count(distinct pno) from ttt group by sno,jno) a where a.ssum=(select count(distinct pno) from ttt where sno='s1')
to:shuichangliu ttt 代表的是什么呀。
请问internetcsdn: 多对多就不能用sql语句了吗?
select jno from spj where sno='s1' AND PNO IN (SELECT PNO FROM SPJ WHERE sno='s1' GROUP BY PNO) group by jno having count(distinct pno)=( select count(distinct pno) from spj where sno='s1' )从 victorycyz(中海) 的答案修改
select distinct pno from spj where sno = 'sl' 我想不会这么简单,楼主的意思不太明白!
to : 各位大侠 首先谢谢各位的参与和发言,对于你们给出的答案我都一一试过了,但是没有找到完全符合我的要求的。 经过一整天的时间,这个问题终于让我解决了。但我觉得我的方法很牵强,应该有更好的办法的。 下面先给出我的实现办法: 1)定义aa视图 //与供应商s1有关的所有工程码jno create view aa as (select distinct jno from spj where (sno='s1')) 2)定义bb视图 //供应商提供的所有零件码pno create view bb as (select distinct pno from spj where (sno='s1')) 3)定义x视图 //请大家仔细看这段,我解释不清,最好自己分段试一下。 create view x as (select jno,count(jno) countNO//countNO为别名 from (select distinct aa.fno, bb.pno from spj, aa, bb where spj.jno=aa.jno and spj.pno=bb.pno) group by jno) 4)最终SQL操作 select jno from x where countNO>=(select count(pno) from bb) 不知道有没有更好的办法,请各位指点。
select distinct jno from spj s1 where sno='s1' and Not exists(select pno from spj s2 where s2.sno='s1'and s2.pno not in (select pno from spj s3 where s1.jno=s3.jno ))
where sno='s1'
group by sno having count(distinct pno) = 零件数量
select jno
from spj
where sno='s1'
group by jno
having count(distinct pno)=( select count(distinct pno)
from spj
where sno='s1'
)
(select sno,jno,[ssum]=count(distinct pno)
from ttt group by sno,jno) a
where a.ssum=(select count(distinct pno)
from ttt where sno='s1')
ttt 代表的是什么呀。
多对多就不能用sql语句了吗?
from spj
where sno='s1'
AND PNO IN (SELECT PNO FROM SPJ WHERE sno='s1' GROUP BY PNO)
group by jno
having count(distinct pno)=( select count(distinct pno)
from spj
where sno='s1'
)从 victorycyz(中海) 的答案修改
我想不会这么简单,楼主的意思不太明白!
99831323(99831323) 结果也是对的,但是画蛇添足了,呵呵
不好意思用了我的测试表名
1)定义aa视图 //与供应商s1有关的所有工程码jno
create view aa
as (select distinct jno
from spj
where (sno='s1')) 2)定义bb视图 //供应商提供的所有零件码pno
create view bb
as (select distinct pno
from spj
where (sno='s1')) 3)定义x视图 //请大家仔细看这段,我解释不清,最好自己分段试一下。
create view x
as (select jno,count(jno) countNO//countNO为别名
from (select distinct aa.fno, bb.pno
from spj, aa, bb
where spj.jno=aa.jno and spj.pno=bb.pno)
group by jno) 4)最终SQL操作
select jno
from x
where countNO>=(select count(pno)
from bb) 不知道有没有更好的办法,请各位指点。
分数等一下就发。
希望能和大家一起交流。
非常对不起,你的答案我又试了一次,可能是当初漏掉了,都怪我,这样,我再给你加40分。
非常对不起,你的答案我又试了一次,也很正确,可能是当初漏掉了,都怪我。
这样,我再给你加40,请接分。
Sorry, 没有看到你最后的两个回复。既然是不小心漏掉了,那就算了。这事到此为止吧。我错怪你了,再次向你说声对不起。