select jno from spj spjx where not exists ( select * from spj spjy where spjy.sno='s1' and not exists ( select * from spj spjz where spjz.jno=spjx.jno and spjy.pno=spjz.pno ) )
select jno, pno from spj where pno in (select pno from spj from sno='s1');orselect jno, pno from spj a where exists (select pno from spj b where sno = 's1' and a.pno = b.pno);
select distict a.jno from spjx as a where sno='s1' and jno not in ( ----去掉部分使用的项目 select jno from spjx where sno='s1' group by jno having count(distinct pno)<( select count(distinct pno) from spjx where sno='s1' ) )
直接的写法是select distict a.jno from spjx as a where sno='s1' and jno not in ( ----去掉部分使用的项目 select jno from ( ---项目同零件的全连接 select jno,pno from( select distinct jno from spjx where sno='s1' ) as k, ( select distinct pno from spjx where sno='s1' ) as l )as b where not exist (select * from spjx c where sno='s1' and b.jno=c.jno and b.pno=c.pno ) )
where not exists
(
select * from spj spjy
where spjy.sno='s1' and not exists
(
select * from spj spjz
where spjz.jno=spjx.jno and spjy.pno=spjz.pno
)
)
where pno in (select pno from spj from sno='s1');orselect jno, pno from spj a
where exists (select pno from spj b
where sno = 's1'
and a.pno = b.pno);
from spjx as a
where sno='s1' and jno not in (
----去掉部分使用的项目
select jno
from spjx
where sno='s1'
group by jno
having count(distinct pno)<(
select count(distinct pno)
from spjx
where sno='s1'
)
)
from spjx as a
where sno='s1' and jno not in (
----去掉部分使用的项目
select jno
from (
---项目同零件的全连接
select jno,pno
from(
select distinct jno
from spjx
where sno='s1'
) as k,
(
select distinct pno
from spjx
where sno='s1'
) as l
)as b
where not exist (select *
from spjx c
where sno='s1'
and b.jno=c.jno and b.pno=c.pno
)
)