SQL如何按条件从各分区中获取符合关联规则的最新记录集

问题背景 #

现有业务表结构如下：

基础需求为取每个category下最新的记录，示例预期返回id=2、4的记录。实际业务需适配两个特殊场景：

• 同一分类下记录以链路成对形式存在，可能超过2条：例如下表中XYZ分类共4条记录，DEF分类共2条记录，预期返回id=3、4、6的记录；如果某分类下共6条3代关联记录，需返回最新一代的3条记录。

  id	status	date	category
  1	PENDING	2022-07-01	XYZ
  2	PENDING	2022-07-02	XYZ
  3	FAILED	2022-07-04	XYZ
  4	FAILED	2022-07-05	XYZ
  5	PENDING	2022-07-03	DEF
  6	DONE	2022-07-08	DEF

• 同一分类下最新批次的记录date字段可能相同，不能仅靠日期排序取单条。

此前尝试的dense_rank()按日期排序取排名1的方案，仅能返回同日期的顶层记录，无法覆盖多代链路的批量返回需求。

补充字段信息 #

表中存在prev_id字段标识记录的关联关系，完整表结构如下：

prev_id值为{}代表是链路起始节点，非空值存储当前记录直接关联的上一级记录id。

实现方案 #

核心思路是先遍历每个分类下的关联链路，给不同代际的记录打标，最终返回每个分类下最新代际的所有记录即可，支持递归的数据库（PostgreSQL、MySQL 8.0+、SQL Server等）可直接使用如下SQL：

WITH RECURSIVE link_trace AS (
    -- 锚点：查询所有链路起始节点，标记代际为1
    SELECT
        id,
        status,
        `date`,
        category,
        prev_id,
        1 AS gen
    FROM tbl
    WHERE prev_id = '{}'

    UNION ALL

    -- 递归关联下一级节点，代际累加
    SELECT
        t.id,
        t.status,
        t.`date`,
        t.category,
        t.prev_id,
        lt.gen + 1 AS gen
    FROM tbl t
    JOIN link_trace lt
        ON t.category = lt.category
        AND CAST(TRIM('{}' FROM t.prev_id) AS UNSIGNED) = lt.id
),
max_gen_per_cat AS (
    -- 计算每个分类的最大代际（最新批次）
    SELECT
        category,
        MAX(gen) AS latest_gen
    FROM link_trace
    GROUP BY category
)
-- 关联返回最新批次的所有记录
SELECT lt.id, lt.status, lt.`date`, lt.category, lt.prev_id
FROM link_trace lt
JOIN max_gen_per_cat mg
    ON lt.category = mg.category
    AND lt.gen = mg.latest_gen
ORDER BY lt.id;

逻辑说明 #

• 递归CTE会自动遍历每个分类下的全量关联链路，从起始节点开始逐层向下匹配，给每层节点分配代际编号，不存在漏匹配问题
• 聚合得到每个分类的最大代际值后，筛选出所有属于该代际的记录，自动适配同日期、多记录、不同链路长度的场景
• 针对给出的示例数据，执行后返回id=3、4、6的记录，完全符合预期；如果分类下存在3代共6条关联记录，会自动返回第三代的全部3条记录。

如果使用不支持递归CTE的数据库版本，可以通过prev_id的关联深度计算代际：例如prev_id为空是第1代，指向第1代的是第2代，指向第2代的是第3代，通过多轮关联打标即可实现相同效果。

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