SQL如何按指定规则获取Cancelled记录对应的前序非Support有效行

AI 小北

2026-9-25

SQL查询问题：查找Cancelled状态对应的最近非Support前置记录

现有查询结果表

你当前已有的SQL返回结果如下：

ID	Date	Description	rn
534	2021-01-01 4:03:14	NEW	1
534	2021-01-01 4:03:24	Payment	2
534	2021-01-01 4:04:14	Accepted	3
534	2021-01-01 4:05:23	Support	4
534	2021-01-01 4:05:32	Cancelled	5
632	2021-01-01 4:03:36	NEW	1
632	2021-01-01 4:04:12	Payment	2
632	2021-01-01 4:06:28	Support	3
632	2021-01-01 4:11:23	Cancelled	4
844	2021-01-01 5:03:36	NEW	1
844	2021-01-01 5:04:12	Payment	2
844	2021-01-01 5:06:28	Accepted	3
844	2021-01-01 5:11:23	Cancelled	4

查询需求

找到所有Description为Cancelled的记录，获取对应同ID的前一条状态记录；如果前一条记录的Description为Support，则继续向前查找最近的非Support状态记录，最终返回匹配的有效行。

预期输出

ID	Date	Description	rn
534	2021-01-01 4:04:14	Accepted	3
632	2021-01-01 4:04:12	Payment	2
844	2021-01-01 5:06:28	Accepted	3

解决方法

方案1：窗口函数法（适用MySQL8.0+、PostgreSQL、Oracle等支持窗口函数的数据库）

WITH original_result AS (
  -- 此处替换为你生成上述结果表的原始SQL语句
  SELECT ID, Date, Description, rn FROM your_table
)
SELECT res.*
FROM original_result res
INNER JOIN (
  SELECT 
    ID,
    MAX(CASE WHEN Description != 'Support' AND rn < cancel_rn THEN rn END) AS target_rn
  FROM (
    SELECT 
      ID,
      rn,
      Description,
      MAX(CASE WHEN Description = 'Cancelled' THEN rn END) OVER (PARTITION BY ID) AS cancel_rn
    FROM original_result
  ) t
  GROUP BY ID
) target_map ON res.ID = target_map.ID AND res.rn = target_map.target_rn;

方案2：自关联法（兼容不支持窗口函数的低版本数据库）

SELECT t1.*
FROM your_original_result t1
INNER JOIN your_original_result t2 
  ON t1.ID = t2.ID 
  AND t1.rn < t2.rn 
  AND t2.Description = 'Cancelled'
  AND t1.Description != 'Support'
GROUP BY t1.ID, t1.Date, t1.Description, t1.rn
HAVING t1.rn = MAX(t1.rn);