在SQL中如何结合GROUP BY和LIMIT取各组TopN-SQL-PHP中文网

mysql 8.0+ 应优先使用窗口函数替代变量或自连接，典型写法为 row_number() over (partition by group_col order by sort_col desc) 并嵌套子查询过滤 rn = 1，语义清晰、结果确定、安全可靠。

在sql中如何结合group by和limit取各组topn

MySQL 8.0+ 直接用窗口函数最稳

MySQL 5.7 及更早版本不支持 ROW_NUMBER()，强行模拟容易出错；8.0+ 建议无条件优先用窗口函数。它语义清晰、结果确定、无需自连接或变量陷阱。

典型写法是：ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col DESC)，再套一层子查询过滤 rn 。

注意 PARTITION BY 字段必须和 GROUP BY 逻辑一致，否则分组错位
ORDER BY 中若存在相同值，ROW_NUMBER() 仍会强制编号（1,2,3…），而 RANK() 会产生并列（1,1,3…），按需选
避免在窗口函数里用别名排序，如 ORDER BY score DESC 要写全字段，别写 ORDER BY s DESC（除非在子查询中已定义）

SELECT user_id, dept, score
FROM (
  SELECT user_id, dept, score,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY score DESC) AS rn
  FROM users
) t
WHERE rn <h3>PostgreSQL 同样走 window function，但 LIMIT PER GROUP 不合法</h3><p>PostgreSQL 支持 <code>ROW_NUMBER()</code> 和 <code>RANK()</code>，语法和 MySQL 8.0+ 几乎一致。但它明确不支持类似 <code>GROUP BY ... LIMIT 3</code> 的写法——那不是 SQL 标准，执行会报错 <code>ERROR: LIMIT # is not allowed with GROUP BY</code>。</p>

不能把 LIMIT 放在 GROUP BY 查询顶层，那是取全局前 N 行，不是每组 TopN
子查询别名必须显式声明（AS t），否则外层 WHERE 无法引用窗口别名
如果要保留在并列情况下取足 N 行（比如分数并列第 3 名有 2 人，想全取），改用 RANK() 并把条件改为 rank

SQLite 需用 correlated subquery 模拟，性能敏感要小心

SQLite 3.25+ 支持窗口函数，但旧版本（尤其嵌入式场景常见）仍得靠相关子查询。核心思路是：对每一行，统计同组中比它更好的记录数，要求该数量

写法冗长，易漏掉 AND 条件导致跨组计数，例如忘记加 t2.dept = t1.dept
没有索引时，复杂度接近 O(n²)，10 万行以上可能秒变秒级延迟
务必给 PARTITION BY 字段 + ORDER BY 字段建联合索引，例如 CREATE INDEX idx_dept_score ON users(dept, score DESC);

SELECT t1.user_id, t1.dept, t1.score
FROM users t1
WHERE (
  SELECT COUNT(*)
  FROM users t2
  WHERE t2.dept = t1.dept AND t2.score > t1.score
) <h3>GROUP BY + LIMIT 在 MySQL 5.7 是错觉，别信网上那些“技巧”</h3><p>你可能见过类似 <code>SELECT * FROM t GROUP BY dept ORDER BY score DESC LIMIT 3</code> 的写法——它根本不是取每组 Top3，而是先按 <code>dept</code> 分组（MySQL 5.7 默认取每组第一行，行为未定义），再对结果整体排序取前 3。结果完全不可控，且在 SQL mode 开启 <code>ONLY_FULL_GROUP_BY</code> 时直接报错 <code>Expression #3 of SELECT list is not in GROUP BY clause</code>。</p>