sql server除法结果为啥未0

关系代数除法与其对应的 SQL 语句

用下表来解释内容。
sc 表：

sno	cno	grade
201215121	1	92
201215121	2	85
201215121	3	88
201215122	2	90
201215122	3	80
201215123	3	90

基本形式

可用 $R(X,Y), S(Y,Z)$ 来表示除法中的一般情况。
这是因为，假如 $R(x_1,\dots x_n,y_1,\dots,y_m),S(y_1,\dots ,y_m,z_1,\dots,z_k)$
令 $X=(x_1,\dots,x_n),Y=(y_1,\dots,y_m),Z=(z_1,\dots,z_k)$
这样一来，
$X$ 表示在 $R$ 中却不在 $S$ 中的列
$Y$ 表示既在 $R$ 中也在 $S$ 中的列
$Z$ 表示在 $S$ 中却不在 $R$ 中的列
可以将关系 $R$ 看成只有 $X,Y$ 两列，$S$ 亦然，而例如判定 $R$ 中两个元组的 $Y$ 属性是否相等，则是判定两个元组的 $Y$

象集

对关系代数除法的解释，需要借助一个叫做象集的概念。
$t\in R$ 表示 t 是 R 的一个元组
$t[X_i]$ 表示元组 $t$ 中相应于属性 $X$ 的一个分量（$t.X_i$ 吧大概）
设 $x\in X$，则 $x$ 在 R 中的象集为：
$Y_x=\{t[Y]|t\in R\wedge t[X]=x\}$

例如在 sc 表中，201215121 的象集就是

cnograde192285388

和 group by 有相似之处

关系代数除法的定义

除法的定义式为（设除法的结果是关系 $P(X)$）：

$P(X)=R\div S=\{t_r[X]|t_r\in R\wedge\Pi_Y(S)\subseteq Y_{t_r[X]}\}$

其中，$Y_{t_r[X]}=\{t[Y]|t\in R\wedge t[X]=t_r[X]\}$，也就是说，是 $t_r[X]$ 在 $R$

我这样解释除法：

• 遍历 $R$ 中的每一个元组时，考察其 $X$
• 如果如果 $S$ 中的整个 $Y$ 行是一个元组中的 $X$ 分量在 $R$ 中的象集的子集，则输出这个 $X$
• 对所有输出的 $X$

按除法的定义，可知：

• $(R\div S)\times S=R$
• $(R\times S)\div S=R$

笛卡尔积的逆运算是这种除法，但这种除法的逆运算不是笛卡尔积，毋宁说想找到这个除法的逆运算是困难的。

例如：
给定这样一个表 $S$：

cno

1

2

考虑 $\Pi_{sno,cno}(sc)\div S$
考虑 $\Pi_{sno,cno}(sc)$ 的第一行，201215121 在 $\Pi_{sno,cno}(sc)$

cno

1

2

3

显然，$S$ 是这个象集的子集，因此第一行中的 $sno$ 会输出，第二、三行亦然。
但是第 4 ~ 6 行则不会输出，因为 $S$ 不是这几行中的 $sno$

因此，$\Pi_{sno,cno}(sc)\div S$ 的结果就是：只有一列 $sno$，只有一行 201215121 的表。
而显然，$(\Pi_{sno,cno}(sc)\div S)\times S$ 只会有两行，因此不等于 $\Pi_{sno,cno}(sc)$

Mysql 实现关系代数除法

Mysql 并不提供直接实现除法的语法，但是可以通过现有语法实现和除法相同的效果。

预计使用 mysql 的 where 语句+条件表达式的形式来实现除法。
首先考察如何理解 mysql 中的 where 语句：

• 遍历这个表中的每一个元组
• 判定遍历到这个元组时 where 语句是否为真
• 如果为真，则输出这个元组

再来考察除法的定义式与我对除法的解释：

$P(X)=R\div S=\{t_r[X]|t_r\in R\wedge\Pi_Y(S)\subseteq \{t[Y]|t\in R\wedge t[X]=t_r[X]\}\}$

• 遍历 $R$ 中的每一个元组时，考察其 $X$
• 如果如果 $S$ 中的整个 $Y$ 行是一个元组中的 $X$ 分量在 $R$ 中的象集的子集，则输出这个 $X$
• 对所有输出的 $X$

我的解释提到了遍历的概念且解释了每次遍历时的判定方法

现尝试给出某一 $t_r\in R$ 满足条件的逻辑表达式：
$\forall y,y\in\Pi_Y(S)\to y\in\{t[Y]|t\in R(X,Y)\wedge t[X]=t_r[X]\}\}\\ \forall y,y\in\Pi_Y(S)\to (\exist t,t\in R\wedge t[X]=t_r[X]\wedge y=t[Y])\}\\ \neg\exist y,\neg(y\in\Pi_Y(S)\to (\exist t,t\in R\wedge t[X]=t_r[X]\wedge y=t[Y])\})\\ \neg\exist y,\neg(y\not\in\Pi_Y(S)\vee (\exist t,t\in R\wedge t[X]=t_r[X]\wedge y=t[Y])\})\\ \neg\exist y,y\in\Pi_Y(S)\wedge (\neg\exist t,t\in R\wedge t[X]=t_r[X]\wedge y=t[Y])\}$
这样一来，可以用这样的 mysql 语句实现 $R(X,Y)\div S(Y,Z)$

select A.X from R as A
where not exists
    select * from R as B(
    where B.Y in (
        select S.Y from S
    )and not exists(
        select * from R as C
        where A.X=C.X and B.y=C.Y
    )
)

值得一提的是，这里的 B.Y in(...) 可以替换成别的逻辑表达式，表示 $B.Y$

测试：
尝试使用 Mysql 实现 $\Pi_{sno,cno}(sc)\div S$

select distinct A.sno from (select sno, cno from sc) as A
where not exists(
    select * from (select sno, cno from sc) as B
    where B.cno in ('1', '2') and not exists(
        select * from (select sno, cno from sc) as C
        where A.sno = C.sno and B.cno = C.cno
    )
)

当然，这是严格按照除法定义写出的，也可以直接写成：

select distinct A.sno from sc as A
where not exists(
    select * from sc as B
    where B.cno in ('1', '2') and not exists(
        select * from sc as C
        where A.sno = C.sno and B.cno = C.cno
    )
)

这是因为在实际上的判断语句中并没有涉及到 sc 中 sno 和 cno 以外的列。

两个 mysql 语句输出均符合预期：

+-----------+
| sno       |
+-----------+
| 201215121 |
+-----------+

例题

我开始关注的关系代数除法是因为这样一道题：求至少选修了学号为S1所选修的全部课程的学生学号。

给出的示例表就是这道题所用的表。

答案是：

set @S1 = "201215122";

select distinct sno from sc as x
where not EXISTS(
    select * from sc as y
    where y.sno=@S1 and not exists(
        select * from sc as z 
        where z.sno=x.sno and z.cno=y.cno
    )
);

观察题意，只需选出 S1 所选的全部 cno，与 sc 表相除即可得到答案。
这里判断 y.sno=@S1 就是判定 $y$

其他解法

select sno from
(
    select a.sno, COUNT(b.cno) as c from
    (
        select sc.cno from sc
        where sc.sno = @S1
    )as b
    join sc as a
    where a.cno = b.cno
    group by sno
)as ttt 
join (
    select MAX(ttt2.c) as ma from (
        select a.sno, COUNT(b.cno) as c from
        (
            select sc.cno from sc
            where sc.sno = @S1
        )as b
        join sc as a
        where a.cno = b.cno
        group by sno
    ) as ttt2
) as af
where c = ma and sno <> @S1;

计算每个人的 cno 的集合与 S1 的 cno 的交集，如果这个交集的元素个数与 S1 的 cno 数量相同，说明 S1 的 cno 是这个象集的子集，满足条件，可输出。也是实现除法的另一种方法，你可以考虑一下这种思路来提出一种新的用 sql 语句实现除法的方法。