SQL自连接实现Top4记录重排：移除行匹配升级行问题求助

这个问题的核心是一对一匹配Top4移除行和Top4以下的升级行，且升级行只能被使用一次，用窗口函数给两组行分别编号后按序号关联是最直接的方式。以下是通用SQL实现（兼容大多数支持窗口函数的数据库如PostgreSQL、MySQL 8.0+、SQL Server等）：

WITH 
-- 1. 筛选Top4中的移除行（A组），按rank升序编号
top4_remove_rows AS (
    SELECT 
        id,
        rank,
        ROW_NUMBER() OVER (ORDER BY rank) AS match_seq
    FROM your_table
    WHERE rank <= 4 AND remove = true
),
-- 2. 筛选Top4以下的升级行（B组），按rank升序编号（最近的排前面）
below_top4_upgrade_rows AS (
    SELECT 
        id,
        rank,
        ROW_NUMBER() OVER (ORDER BY rank) AS match_seq
    FROM your_table
    WHERE rank > 4 AND upgrade = true
),
-- 3. 按编号一对一关联A组和B组
matched_pairs AS (
    SELECT 
        a.id AS a_id,
        b.id AS b_id
    FROM top4_remove_rows a
    LEFT JOIN below_top4_upgrade_rows b ON a.match_seq = b.match_seq
)
-- 4. 生成最终结果，处理不同行的new_id逻辑
SELECT 
    t.id,
    t.rank,
    t.remove,
    t.upgrade,
    CASE
        -- 非移除行：new_id为自身ID
        WHEN t.remove = false THEN t.id
        -- 移除行：取匹配到的升级行ID，无匹配则为null
        WHEN t.remove = true THEN mp.b_id
        -- 升级行：new_id为null
        WHEN t.upgrade = true THEN null
    END AS new_id
FROM your_table t
LEFT JOIN matched_pairs mp ON t.id = mp.a_id
ORDER BY t.rank;

关键逻辑说明： #

• ROW_NUMBER()窗口函数：给两组行分别按rank排序编号，确保第一个移除行匹配第一个可用的升级行，第二个移除行匹配第二个，从根源避免升级行被重复关联。
• CASE分支处理：严格对应需求规则，区分非移除行、移除行、升级行的new_id生成逻辑。
• 左连接保留全量行：即使移除行没有匹配到升级行（比如升级行数量不足），也会完整出现在结果中，new_id自动为null。

示例验证： #

假设你的原始数据为：

运行SQL后结果完全符合需求：

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