自言自语

I'm Wang Xianyuan, writing for myself, more studying, more experience…

关系代数中的除怎么用SQL表示

By

问题:

有四个表
供应商表S(sno,sname,city)
零件表P(pno,pname,color,weight)
工程项目表J(jno,jname,city)
供应情况表SPJ(sno,pno,jno,qty)
有一题是这样的:
求至少用了s1供应商所供应的全部零件的工程号jno 

 

解答:

设有
表X (A,B,C,D)
表Y (C,D)–C,D列必须与X表的C,D列在相同的域上定义
那么表X除表Y的结果集为

select distinct A,B
from X
join Y on Y.C = X.C and Y.D = X.D

或者

select distinct A,B
from X
where exists(select 1 from Y where Y.C = X.C and Y.D = X.D) 

[code:tsql]

SELECT jno
FROM J a
WHERE NOT EXISTS(
       
SELECT 1
       
FROM SPJ b
       
WHERE sno = s1

AND a.jno = b.jno
           
AND NOT EXISTS(
               
SELECT 1
               
FROM J c
               
WHERE c.jno = b.jno 
            )
           
    )

[/code]

Leave a Reply