sql - 如何在 SQL Server 中实现 DIVIDE 操作的关系等价物

Question

我正在阅读其中一本教科书中的关系代数。
我遇到了DIVIDE operation .来自维基百科:

The division is a binary operation that is written as R ÷ S. The result consists of the restrictions of tuples in R to the attribute names unique to R, i.e., in the header of R but not in the header of S, for which it holds that all their combinations with tuples in S are present in R.

因此，如果 R 是:

+----+----+
| A  | B  |
+----+----+
| a1 | b1 |
| a2 | b1 |
| a3 | b1 |
| a4 | b1 |
| a1 | b2 |
| a3 | b2 |
| a2 | b3 |
| a3 | b3 |
| a4 | b3 |
| a1 | b4 |
| a2 | b4 |
| a3 | b4 |
+----+----+

S 是:

+----+
| A  |
+----+
| a1 |
| a2 |
| a3 |
+----+

那么输出应该是:

+----+
| B  |
+----+
| b1 |
| b4 |
+----+

现在，本书给出了计算 DIVIDE 运算的关系等效步骤，如下所示。
我试图在 SQL Server 中完全模仿它，但没有方向

T1 ← πBR//即 SELECT A FROM R
T2 ← πB((S x T1) - R)//SxT1 可以简单地做为 SELECT * FROM S,T1
T ← T1 - T2

Answer 1

您的公式有误。在您的特定情况下，您需要在 B 上进行投影而不是投影在 A不然怎么了B属性会出现在结果中吗？

  T1 ← πBR                
  T2 ← πB((S x T1) - R)                                     
  T ← T1 - T2  

查询:

-- T ← T1 - T2 -------------------------------------
SELECT DISTINCT B FROM R -- T1 ← πBR              --
WHERE B NOT IN (                                  --
    -- T2 ← πB((S x T1) - R) -------------------  --
    SELECT CR.B                               --  --
    FROM (                                    --  --
        -- (S x T1) --------------------      --  --
        SELECT S.A, R2.B              --      --  --
        FROM S                        --      --  --
        CROSS JOIN (                  --      --  --
            -- T1 ← πBR               --      --  --
            SELECT DISTINCT B FROM R  --      --  --
        ) AS R2                       --      --  --
        --------------------------------      --  --
    ) AS CR                                   --  --
    WHERE NOT EXISTS (                        --  --
        SELECT *                              --  --
        FROM R                                --  --
        WHERE R.A = CR.A AND R.B = CR.B       --  --
    )-------------------------------------------  --
)---------------------------------------------------

结果:

B
--
b1
b4

SQLFiddle