递归CTE遍历赛事关联层级：查询错误排查请求

问题背景 #

现有存储体育赛事的recursive_test表，字段包括match_id、match_date、p1_id、p2_id。需求是编写递归CTE，输入指定match_id后，返回该赛事两名选手的所有未来赛事，以及这些未来赛事中选手的后续所有关联赛事。以match_id=6为例，预期返回match_id为7、8、9、11、13、15的赛事。

用户编写的递归CTE查询如下：

WITH RECURSIVE match_ids AS (
    SELECT
        m1.match_id,
        m1.match_date,
        m1.p1_id,
        m1.p2_id
    FROM recursive_test AS m1
    WHERE m1.match_id = 6
    UNION ALL
    SELECT
        m2.match_id,
        m2.match_date,
        m2.p1_id,
        m2.p2_id
    FROM recursive_test AS m2
    INNER JOIN match_ids
        ON (
            match_ids.p1_id = m2.p1_id
            OR match_ids.p1_id = m2.p2_id
            OR match_ids.p2_id = m2.p1_id
            OR match_ids.p2_id = m2.p2_id
        )
            AND match_ids.match_date > m2.match_date
)
SELECT match_id
FROM match_ids

但该查询返回了包含6、2、4等不符合预期的match_id列表。

建表及插入数据语句：

CREATE TABLE `recursive_test` (
  `match_id` int NOT NULL,
  `match_date` date NOT NULL,
  `p1_id` int NOT NULL,
  `p2_id` int NOT NULL,
  PRIMARY KEY (`match_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `recursive_test` VALUES (1,'2022-01-01',1,2),(2,'2022-01-02',3,1),(3,'2022-01-03',3,4),(4,'2022-01-04',2,3),(5,'2022-01-05',5,6),(6,'2022-01-06',1,2),(7,'2022-01-07',3,1),(8,'2022-01-08',3,4),(9,'2022-01-09',2,3),(10,'2022-01-10',5,6),(11,'2022-01-11',3,4),(12,'2022-01-12',7,8),(13,'2022-01-13',3,1),(14,'2022-01-14',5,7),(15,'2022-01-15',4,5);

错误原因分析 #

1. 日期条件逻辑颠倒：查询中使用match_ids.match_date > m2.match_date，这会筛选出早于当前递归层级赛事日期的历史赛事，而非需求中的未来赛事。正确逻辑应为m2.match_date > match_ids.match_date，确保只获取当前赛事之后举办的赛事。
2. 未排除初始赛事：递归CTE的初始查询包含了输入的match_id=6，而需求要求返回的是该赛事的未来关联赛事，因此最终结果需要排除这个初始ID。
3. 未处理重复数据：使用UNION ALL会保留递归过程中重复获取的赛事记录，导致结果可能出现重复的match_id。

修正后的查询 #

WITH RECURSIVE match_ids AS (
    SELECT
        m1.match_id,
        m1.match_date,
        m1.p1_id,
        m1.p2_id
    FROM recursive_test AS m1
    WHERE m1.match_id = 6
    UNION
    SELECT
        m2.match_id,
        m2.match_date,
        m2.p1_id,
        m2.p2_id
    FROM recursive_test AS m2
    INNER JOIN match_ids
        ON (
            match_ids.p1_id = m2.p1_id
            OR match_ids.p1_id = m2.p2_id
            OR match_ids.p2_id = m2.p1_id
            OR match_ids.p2_id = m2.p2_id
        )
            AND m2.match_date > match_ids.match_date
)
SELECT DISTINCT match_id
FROM match_ids
WHERE match_id != 6;

修正说明 #

• 反转日期条件，确保只获取当前赛事之后的未来赛事；
• 使用UNION替代UNION ALL，自动去重递归过程中重复匹配的赛事；
• 最终查询过滤掉初始的match_id=6，符合需求中“未来赛事”的要求；
• 添加DISTINCT进一步确保结果无重复（和UNION配合形成双重保障）。

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