如何编写SQL实现按用户取Stat1最大值、Stat2降序的TOP20查询？

需要实现：

• 每个用户仅保留Stat1值最高的记录
• 若同一用户有多条Stat1值相同的最高记录，保留其中Stat2值最高的那条
• 最终从所有符合条件的记录中取TOP20，按Stat1降序、Stat2降序、Stat3降序排序

示例数据 #

期望结果 #

原查询问题分析 #

原查询仅通过子查询筛选出用户Stat1等于自身最大值的记录，但无法处理同一用户多条Stat1同为最大值的情况，导致这类用户的多条记录被全部返回，不符合“每个用户仅留一条”的要求。

解决方案：使用窗口函数ROW_NUMBER() #

针对SQL Server（原查询使用TOP语法，推测为SQL Server环境），最简洁高效的方法是使用ROW_NUMBER()窗口函数，按用户分组后排序标记：

SELECT TOP 20
    UID,
    ISNULL(Stat1, 0) AS Stat1,
    ISNULL(Stat2, 0) AS Stat2,
    ISNULL(Stat3, 0) AS Stat3
FROM (
    SELECT
        UID,
        Stat1,
        Stat2,
        Stat3,
        -- 按UID分组，组内按Stat1降序、Stat2降序排序，标记每条记录的序号
        ROW_NUMBER() OVER (PARTITION BY UID ORDER BY Stat1 DESC, Stat2 DESC) AS rn
    FROM TABLE
) AS ranked
-- 仅保留每个用户排序第一的记录（即Stat1最高，Stat2最高的那条）
WHERE rn = 1
-- 最终排序规则
ORDER BY Stat1 DESC, Stat2 DESC, Stat3 DESC

代码说明 #

1. 子查询中的窗口函数：PARTITION BY UID将数据按用户分组，ORDER BY Stat1 DESC, Stat2 DESC确保组内先按Stat1从高到低排序，Stat1相同时按Stat2从高到低排序，ROW_NUMBER()为每组内的记录生成序号，序号为1的就是该用户需要保留的那条记录。
2. 外层筛选：通过WHERE rn = 1过滤掉每个用户的冗余记录，只保留目标记录。
3. TOP20与排序：最后取前20条，并按要求排序。

替代方案：关联子查询匹配双最大值 #

如果无法使用窗口函数（如旧版SQL环境），可以通过关联子查询同时匹配Stat1和Stat2的最大值：

SELECT TOP 20
    t1.UID,
    ISNULL(t1.Stat1, 0) AS Stat1,
    ISNULL(t1.Stat2, 0) AS Stat2,
    ISNULL(t1.Stat3, 0) AS Stat3
FROM TABLE t1
WHERE (t1.Stat1, t1.Stat2) = (
    SELECT MAX(t2.Stat1), MAX(t2.Stat2)
    FROM TABLE t2
    WHERE t2.UID = t1.UID
      AND t2.Stat1 = (SELECT MAX(t3.Stat1) FROM TABLE t3 WHERE t3.UID = t1.UID)
)
ORDER BY Stat1 DESC, Stat2 DESC, Stat3 DESC

说明 #

子查询先找到用户的Stat1最大值，再在该Stat1范围内找到Stat2的最大值，最终匹配同时满足这两个条件的记录。

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