如何基于ID组合与各ID最高分数筛选数据表行？

嘿，我来帮你搞定这个问题！你的需求很明确：要保留那些同时满足id_1的score_1是该ID的最高分、id_2的score_2是该ID的最高分的行对吧？我猜你之前用自连接没成功，大概率是没同时兼顾两个ID的最高分条件，或者关联逻辑没写全——没关系，我给你两种靠谱的SQL方案：

方法1：用窗口函数（推荐，逻辑清晰） #

窗口函数可以很方便地给每个ID分组下的分数做排名，之后筛选出两个排名都是第一的行就行。这里用ROW_NUMBER()，如果有并列最高分想全部保留，可以换成RANK()或者DENSE_RANK()：

WITH ranked_scores AS (
    SELECT 
        id_1,
        score_1,
        id_2,
        score_2,
        -- 给每个id_1分组下的score_1按降序排名
        ROW_NUMBER() OVER (PARTITION BY id_1 ORDER BY score_1 DESC) AS rank_1,
        -- 给每个id_2分组下的score_2按降序排名
        ROW_NUMBER() OVER (PARTITION BY id_2 ORDER BY score_2 DESC) AS rank_2
    FROM your_table_name -- 替换成你的表名
)
SELECT id_1, score_1, id_2, score_2
FROM ranked_scores
WHERE rank_1 = 1 AND rank_2 = 1;

逻辑说明： #

1. 先通过CTE（公共表表达式）给每行标记在对应ID分组下的分数排名，最高分的行排名为1；
2. 最后筛选出rank_1和rank_2都等于1的行，就是同时满足两个ID各自最高分的目标行。

方法2：用子查询+连接 #

如果你习惯用子查询，也可以先分别找出每个ID的最高分，再和原表关联匹配：

SELECT t.id_1, t.score_1, t.id_2, t.score_2
FROM your_table_name t
-- 关联id_1的最高分数据
JOIN (
    SELECT id_1, MAX(score_1) AS max_score_1
    FROM your_table_name
    GROUP BY id_1
) m1 ON t.id_1 = m1.id_1 AND t.score_1 = m1.max_score_1
-- 关联id_2的最高分数据
JOIN (
    SELECT id_2, MAX(score_2) AS max_score_2
    FROM your_table_name
    GROUP BY id_2
) m2 ON t.id_2 = m2.id_2 AND t.score_2 = m2.max_score_2;

逻辑说明： #

1. 第一个子查询m1找出每个id_1对应的最高分max_score_1；
2. 第二个子查询m2找出每个id_2对应的最高分max_score_2；
3. 把原表t分别和m1、m2连接，同时匹配ID和对应的最高分，就能得到符合条件的行。

这两种方法都能完美解决你示例中的问题，返回你需要的那一行~

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