SQLite中基于tournaments表查找pinsheets表缺失的赛事轮次数据

问题背景 #

我有两张SQLite表：tournaments和pinsheets，表结构及数据如下：

pinsheets表 #

表结构：

CREATE TABLE "pinsheets" (
    "tournament"    INTEGER,
    "year"  INTEGER,
    "course"    INTEGER,
    "round" INTEGER,
    "hole"  INTEGER,
    "front" INTEGER,
    "side"  INTEGER,
    "region"    INTEGER
);

数据样例：

2   2015    6   1   1   18      C
2   2015    6   1   2   8   4   L
2   2015    6   1   3   22      C
2   2015    6   1   4   45  4   R
2   2015    6   1   5   26  6   L

tournaments表 #

表结构：

CREATE TABLE "tournaments" (
    "tournament"    INTEGER,
    "year"  INTEGER
);

数据样例：

2   2015    
2   2016    
2   2017    
2   2018    
2   2019    

tournaments表存储所有理论上的赛事年份组合，pinsheets表存储实际采集的赛事数据。我需要找出缺失的记录，具体要求是：

• 遍历每个tournament/year组合
• 检查轮次1、2、3、4中，哪些tournament/year/round组合未出现在pinsheets表中

我尝试的SQL语句未得到正确结果：

SELECT *
FROM tournaments t
WHERE NOT EXISTS (
    SELECT *
    FROM pinsheets pin
    WHERE  t.tournament = pin.tournament
    AND t.year = pin.year 
    AND (pin.round = 1 
            OR pin.round = 2
            OR pin.round = 3
            OR pin.round = 4)
)

期望输出：

tournament   year   round
2   2015    3
2   2016    2
2   2017    2

解决方法 #

原SQL逻辑错误：它会筛选出完全没有1-4轮数据的tournament/year组合，但实际需要的是每个赛事年份下缺失的具体轮次。

正确思路是先生成所有应存在的tournament/year/round组合，再排除已存在的记录，剩下的就是缺失项：

正确SQL语句 #

WITH all_rounds AS (
    SELECT 1 AS round UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4
)
SELECT t.tournament, t.year, ar.round
FROM tournaments t
CROSS JOIN all_rounds ar
WHERE NOT EXISTS (
    SELECT 1
    FROM pinsheets pin
    WHERE pin.tournament = t.tournament
      AND pin.year = t.year
      AND pin.round = ar.round
)
ORDER BY t.tournament, t.year, ar.round;

逻辑说明 #

1. all_rounds通过CTE生成包含1-4轮次的临时表
2. CROSS JOIN将每个tournament/year组合与4个轮次配对，得到所有理论上应存在的赛事轮次组合
3. NOT EXISTS子句逐个检查每个组合是否在pinsheets表中存在，不存在的即为缺失的记录

执行该语句后，就能得到符合期望的缺失轮次列表。

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