SQLite中如何查询两表间缺失的tournament_id、year、course_id组合

现有表结构与数据 #

holes表 #

创建表语句：

CREATE TABLE holes (`tournament_id` INTEGER, `year` INTEGER, `course_id` INTEGER, `round` INTEGER, `hole` INTEGER, `front` INTEGER, `side` INTEGER, `region` INTEGER);

示例数据：

INSERT INTO holes (`tournament_id`, `year`, `course_id`, `round`, `hole`, `front`, `side`, `region`) VALUES
  ('33', '2016', '895', '1', '1', '12', '5', 'L'),
  ('33', '2016', '895', '1', '2', '18', '10', 'R'),
  ('33', '2016', '895', '1', '3', '15', '7', 'R'),
  ('33', '2016', '895', '1', '4', '11', '7', 'R'),
  ('33', '2016', '895', '1', '5', '18', '7', 'L'),
  ('33', '2016', '895', '1', '6', '28', '5', 'L'),
  ('33', '2016', '895', '1', '7', '21', '12', 'R');

tournaments表 #

创建表语句：

CREATE TABLE tournaments (`tournament_id` INTEGER, `year` INTEGER, `R1` INTEGER, `R2` INTEGER, `R3` INTEGER, `R4` INTEGER);

其中R1、R2、R3、R4字段为课程ID，示例数据：

INSERT INTO tournaments VALUES
  (33, 2016, 715, 715, 895, 400);

当前尝试的SQL及问题 #

尝试的SQL语句：

WITH h AS (
SELECT DISTINCT tournament_id, year, course_id
FROM holes)

SELECT t.tournament_id, t.year
FROM tournaments t
WHERE NOT EXISTS (
  SELECT *
  FROM h
  WHERE h.tournament_id = t.tournament_id
  AND h.year = t.year
  AND h.course_id IN (t.R1, t.R2, t.R3, t.R4)
);

该语句仅能返回tournament_id和year，无法输出缺失的course_id，期望得到的结果为：

33  2016  715
33  2016  400

解决方案SQL #

可以通过将tournaments表中的R1-R4字段拆分为独立行，再与holes表的去重组合进行左连接，筛选出holes表中不存在的组合：

WITH h AS (
    SELECT DISTINCT tournament_id, year, course_id
    FROM holes
),
tournament_courses AS (
    SELECT tournament_id, year, R1 AS course_id FROM tournaments
    UNION
    SELECT tournament_id, year, R2 AS course_id FROM tournaments
    UNION
    SELECT tournament_id, year, R3 AS course_id FROM tournaments
    UNION
    SELECT tournament_id, year, R4 AS course_id FROM tournaments
)
SELECT tc.tournament_id, tc.year, tc.course_id
FROM tournament_courses tc
LEFT JOIN h ON h.tournament_id = tc.tournament_id 
           AND h.year = tc.year 
           AND h.course_id = tc.course_id
WHERE h.tournament_id IS NULL;

逻辑说明 #

1. 用CTE h 获取holes表中所有唯一的tournament_id、year、course_id组合；
2. 用CTE tournament_courses 将tournaments表中每一行的四个课程ID拆分为单独的行，通过UNION自动去重重复的课程ID；
3. 将两个CTE进行左连接，筛选出holes表中不存在的组合，即为目标结果。

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