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 ALL
INTO R VALUES ('X1','Y1')
INTO R VALUES ('X2','Y2')
INTO R VALUES ('X2','Y3')
INTO R VALUES ('X2','Y1')
SELECT 1 FROM DUAL;

INSERT ALL
INTO S VALUES ('Y1','Z1')
INTO S VALUES ('Y2','Z3')
SELECT 1 FROM DUAL;FROM R;

二、分解 

--R
SELECT * FROM R;

--S
SELECT * FROM S;

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

--W
SELECT 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;

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

--M
SELECT 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÷S
SELECT M.X FROM M RIGHT JOIN N ON M.Y_NUM = N.Y_NUM;

三、综合

--R÷S
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;

--方法二
SELECT DISTINCT X
  FROM R RX
 WHERE NOT EXISTS (SELECT *
          FROM S
         WHERE NOT EXISTS (SELECT *
                  FROM R
                 WHERE R.Y = S.Y
                   AND RX.X = R.X));