关于自关联SQL查询输出3条结果的原理及自关联逻辑问询

表数据 #

SQL语句 #

SELECT DISTINCT A.MNAME 
FROM MEM A
WHERE 2 >= (SELECT COUNT (*)
            FROM MEM B 
            WHERE A.MID > B.MID) ;

查询输出 #

Ben Hung
Greg Chan
Rita Lam

一、为什么输出3条结果？ #

先把所有MID按从小到大排序：10001（Rita Lam）、20060（Ben Hung）、20088（Greg Chan）、30005（Alice Li）、30110（Clara Ng）、40001（Tim lee）、40002（Yan Chan）。

子查询的作用是：对A表的每一行，统计B表中MID比当前A行MID小的记录总数。主查询的条件是这个统计数≤2——意思就是当前A行的MID，在所有MID里比它小的最多有2个，也就是它是排序后的前3名（第1名比0个小，第2名比1个小，第3名比2个小，都满足≤2；第4名及以后的，比它小的数量都超过2，不满足）。

逐个计算每个MID对应的统计数：

• Rita Lam（10001）：没有更小的MID，COUNT=0 → 0≤2，符合条件
• Ben Hung（20060）：仅10001比它小，COUNT=1 →1≤2，符合条件
• Greg Chan（20088）：10001、20060比它小，COUNT=2 →2≤2，符合条件
• Alice Li（30005）：3个MID比它小，COUNT=3 →3>2，不符合
• Clara Ng（30110）：4个MID比它小，COUNT=4 →不符合
• Tim lee（40001）：5个MID比它小，COUNT=5 →不符合
• Yan Chan（40002）：6个MID比它小，COUNT=6 →不符合

最终只有前3条符合条件，所以输出3条结果。

二、自关联逻辑与执行过程 #

这个查询用了自关联（自连接），把同一张MEM表通过别名A、B拆成两张逻辑上独立的表来用，具体逻辑和执行步骤如下：

核心逻辑 #

• A作为主表，逐条遍历所有记录；
• B作为子查询的数据源，每次针对A当前的记录，找出所有B中MID小于A.MID的行，统计数量；
• 主查询只保留统计数量≤2的A表记录，最后通过DISTINCT去重（这里MNAME无重复，所以DISTINCT实际不影响结果）。

执行步骤 #

1. 主查询从A表取出第一条记录：Rita Lam（MID=10001）；
2. 执行子查询：遍历B表所有记录，判断B.MID < 10001，没有符合的记录，COUNT结果为0；
3. 判断2 >= 0，条件成立，这条记录被保留；
4. 主查询取出下一条记录：Ben Hung（MID=20060）；
5. 子查询统计B中MID < 20060的记录，只有10001，COUNT结果为1；
6. 判断2 >=1，条件成立，这条记录被保留；
7. 主查询取出第三条记录：Greg Chan（MID=20088）；
8. 子查询统计B中MID <20088的记录，有10001、20060，COUNT结果为2；
9. 判断2>=2，条件成立，这条记录被保留；
10. 主查询继续取出后续的Alice Li、Clara Ng等记录，子查询统计的COUNT值都大于2，不满足条件，被过滤；
11. 最后对保留的MNAME去重，输出结果。

内容的提问来源于stack exchange，提问作者TC CS