[转] 分组排序取前N条记录以及生成自动数字序列，类似group by后 limit

最新推荐文章于 2025-09-09 08:37:13 发布

转载最新推荐文章于 2025-09-09 08:37:13 发布 · 100 阅读

文章标签：

本文介绍了一种在MySQL中实现类似Oracle的ROW_NUMBER()函数的方法，通过动态SQL构造序列号并进行分组，最终实现按指定字段分组并取每组前几条记录的需求。

前言：

同事的业务场景是，按照cid、author分组，再按照id倒叙，取出前2条记录出来。

oracle里面可以通过row_number() OVER (PARTITION BY cid,author ORDER BY id DESC) 表示根据cid,author分组，在分组内部根据id排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)，而mysql数据库就没有这样的统计函数，需要自己写复杂的sql来实现。

使用动态sql来实现

先构造序列号码，引入一个@row来做rownumber
SET @row=0;SET @mid='';SELECT cid, author, @row:=@row+1 rownum FROM test ORDER BY cid, author LIMIT 10;

序列号码已经出来了，再加一个@mid来进行分组，重点在于CASE WHEN @mid = author THEN @row:=@row+1 ELSE @row:=1 END rownum，表示分组的时候会自动从1计数指导这个分组数据遍历结束。
SET @row=0;SET @mid='';SELECT cid, author,CASE WHEN @mid = author THEN @row:=@row+1 ELSE @row:=1 END rownum, @mid:=author FROM test ORDER BY cid,author DESC LIMIT 20;

好了，再外面加一层inner JOIN 再对 rownumber 做限制就可以拿到目标数据了。
SET @row=0;
SET @mid='';
SELECT a.*,b.rownum FROM test a
INNER JOIN (
SELECT cid, author, id, CASE WHEN @mid = author THEN @row:=@row+1 ELSE @row:=1 END rownum, @mid:=author MID
FROM test
ORDER BY cid,author,id DESC
) b ON b.author=a.author AND b.cid=a.cid AND b.id=a.id WHERE b.rownum<3;

亲试有效哦

转载于：http://blog.itpub.net/26230597/viewspace-1267325/