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

最新推荐文章于 2022-11-13 10:09:46 发布

林震南

最新推荐文章于 2022-11-13 10:09:46 发布

阅读量1w

点赞数

分类专栏： mysql develeper

本文链接：https://blog.csdn.net/csdnhsh/article/details/93376825

版权

mysql develeper 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

前言：

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

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

1，录入测试数据

USE csdn;

DROP TABLE IF EXISTS test;

CREATE TABLE test (

id INT PRIMARY KEY,

cid INT,

author VARCHAR(30)

) ENGINE=INNODB;

INSERT INTO test VALUES

(1,1,\'test1\'),

(2,1,\'test1\'),

(3,1,\'test2\'),

(4,1,\'test2\'),

(5,1,\'test2\'),

(6,1,\'test3\'),

(7,1,\'test3\'),

(8,1,\'test3\'),

(9,1,\'test3\'),

(10,2,\'test11\'),

(11,2,\'test11\'),

(12,2,\'test22\'),

(13,2,\'test22\'),

(14,2,\'test22\'),

(15,2,\'test33\'),

(16,2,\'test33\'),

(17,2,\'test33\'),

(18,2,\'test33\');

INSERT INTO test VALUES (200,200,\'200test_nagios\');

2，原始的效率比较低下的子查询实现方式
SQL代码如下：

SELECT * FROM test a

WHERE

N>(

SELECT COUNT(*)

FROM test b

WHERE a.cid=b.cid AND a.`author`=b.`author` AND a.id<b.id

)ORDER BY cid,author,id DESC;

只要将N换成你要的数字比如2，就表示查询出每个分组的前2条记录，如下所示：

mysql> SELECT * FROM test a

-> WHERE

-> 2>(

-> SELECT COUNT(*)

-> FROM test b

-> WHERE a.cid=b.cid AND a.`author`=b.`author` AND a.id<b.id

-> )ORDER BY cid,author,id DESC;

+-----+------+----------------+

| id | cid | author |

+-----+------+----------------+

| 2 | 1 | test1 |

| 1 | 1 | test1 |

| 5 | 1 | test2 |

| 4 | 1 | test2 |

| 9 | 1 | test3 |

| 8 | 1 | test3 |

| 11 | 2 | test11 |

| 10 | 2 | test11 |

| 14 | 2 | test22 |

| 13 | 2 | test22 |

| 18 | 2 | test33 |

| 17 | 2 | test33 |

| 200 | 200 | 200test_nagios |

+-----+------+----------------+

13 ROWS IN SET (0.00 sec)

mysql>

3，使用动态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;

执行结果如下所示：