oracle 關係除法

標籤：

2016-02-16

關係除法 R÷S

關係模式 R(X,Y) S(Y,Z)

含義：在R中查詢與S中所有元組有關係的元組

一、建立基礎資料表R和S

CREATE TABLE R (X VARCHAR2(10),Y VARCHAR2(10));CREATE TABLE S (Y VARCHAR2(10),Z VARCHAR2(10));INSERT ALLINTO R VALUES (‘X1‘,‘Y1‘)INTO R VALUES (‘X2‘,‘Y2‘)INTO R VALUES (‘X2‘,‘Y3‘)INTO R VALUES (‘X2‘,‘Y1‘)SELECT 1 FROM DUAL;INSERT ALLINTO S VALUES (‘Y1‘,‘Z1‘)INTO S VALUES (‘Y2‘,‘Z3‘)SELECT 1 FROM DUAL;FROM R;

二、分解 

--RSELECT * FROM R;

--SSELECT * FROM S;

--TSELECT Y FROM S;CREATE TABLE T AS SELECT Y FROM S;SELECT * FROM T;

--WSELECT R.X, R.Y FROM (SELECT Y FROM S) T LEFT JOIN R ON T.Y = R.Y;CREATE TABLE W AS SELECT R.X, R.Y FROM (SELECT Y FROM S) T LEFT JOIN R ON T.Y = R.Y;SELECT * FROM W;

--NSELECT COUNT(*) Y_NUM FROM T;CREATE TABLE N AS SELECT COUNT(*) Y_NUM FROM T;SELECT * FROM N;

--MSELECT X, COUNT(*) Y_NUM FROM W GROUP BY X;CREATE TABLE M AS SELECT X, COUNT(*) Y_NUM FROM W GROUP BY X;SELECT * FROM M;

--R÷SSELECT M.X FROM M RIGHT JOIN N ON M.Y_NUM = N.Y_NUM;

三、綜合

SELECT M.X  FROM (SELECT X, COUNT(*) Y_NUM          FROM (SELECT R.X, R.Y                  FROM (SELECT Y FROM S) T                  LEFT JOIN R                    ON T.Y = R.Y) W         GROUP BY X) M RIGHT JOIN (SELECT COUNT(*) Y_NUM FROM (SELECT Y FROM S) T) N    ON M.Y_NUM = N.Y_NUM;

 

oracle 關係除法