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

转载 2015年07月08日 11:05:08
前言:

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

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


1,录入测试数据

  1. USE csdn;
  2. DROP TABLE IF EXISTS test;
  3. CREATE TABLE test (
  4.   id INT PRIMARY KEY,
  5.   cid INT,
  6.   author VARCHAR(30)
  7. ) ENGINE=INNODB;


  8. INSERT INTO test VALUES 
  9. (1,1,\'test1\'),
  10. (2,1,\'test1\'),
  11. (3,1,\'test2\'),
  12. (4,1,\'test2\'),
  13. (5,1,\'test2\'),
  14. (6,1,\'test3\'),
  15. (7,1,\'test3\'),
  16. (8,1,\'test3\'),
  17. (9,1,\'test3\'),
  18. (10,2,\'test11\'),
  19. (11,2,\'test11\'),
  20. (12,2,\'test22\'),
  21. (13,2,\'test22\'),
  22. (14,2,\'test22\'),
  23. (15,2,\'test33\'),
  24. (16,2,\'test33\'),
  25. (17,2,\'test33\'),
  26. (18,2,\'test33\');
  27. INSERT INTO test VALUES (200,200,\'200test_nagios\');

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


  1. SELECT * FROM test a 
  2. WHERE 
  3. N>(
  4.     SELECT COUNT(*) 
  5.     FROM test b
  6.     WHERE a.cid=b.cid AND a.`author`=b.`author` AND a.id<b.id
  7. )ORDER BY cid,author,id DESC;

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

  1. mysql> SELECT * FROM test a 
  2.     -> WHERE 
  3.     -> 2>(
  4.     -> SELECT COUNT(*) 
  5.     -> FROM test b
  6.     -> WHERE a.cid=b.cid AND a.`author`=b.`author` AND a.id<b.id
  7.     -> )ORDER BY cid,author,id DESC;
  8. +-----+------+----------------+
  9. | id | cid | author |
  10. +-----+------+----------------+
  11. | 2 | 1 | test1 |
  12. | 1 | 1 | test1 |
  13. | 5 | 1 | test2 |
  14. | 4 | 1 | test2 |
  15. | 9 | 1 | test3 |
  16. | 8 | 1 | test3 |
  17. | 11 | 2 | test11 |
  18. | 10 | 2 | test11 |
  19. | 14 | 2 | test22 |
  20. | 13 | 2 | test22 |
  21. | 18 | 2 | test33 |
  22. | 17 | 2 | test33 |
  23. | 200 | 200 | 200test_nagios |
  24. +-----+------+----------------+
  25. 13 ROWS IN SET (0.00 sec)


  26. 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;  


执行结果如下所示:

  1. mysql> SET @row=0;
  2. QUERY OK, 0 ROWS affected (0.00 sec)


  3. mysql> SET @mid=\'\';
  4. QUERY OK, 0 ROWS affected (0.00 sec)


  5. mysql> SELECT a.*,b.rownum FROM test a 
  6.     -> INNER JOIN (
  7.     -> SELECT cid, author, id, CASE WHEN @mid = author THEN @row:=@row+ELSE @row:=END rownum, @mid:=author MID 
  8.     -> FROM test 
  9.     -> ORDER BY cid,author,id DESC
  10.     -> ) b ON b.author=a.author AND b.cid=a.cid AND b.id=a.id WHERE b.rownum<3; 
  11. +-----+------+----------------+--------+
  12. | id | cid | author | rownum |
  13. +-----+------+----------------+--------+
  14. | 2 | 1 | test1 | 1 |
  15. | 1 | 1 | test1 | 2 |
  16. | 5 | 1 | test2 | 1 |
  17. | 4 | 1 | test2 | 2 |
  18. | 9 | 1 | test3 | 1 |
  19. | 8 | 1 | test3 | 2 |
  20. | 11 | 2 | test11 | 1 |
  21. | 10 | 2 | test11 | 2 |
  22. | 14 | 2 | test22 | 1 |
  23. | 13 | 2 | test22 | 2 |
  24. | 18 | 2 | test33 | 1 |
  25. | 17 | 2 | test33 | 2 |
  26. | 200 | 200 | 200test_nagios | 1 |
  27. +-----+------+----------------+--------+
  28. 13 ROWS IN SET (0.01 sec)


  29. mysql>

相关文章推荐

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

前言:        同事的业务场景是,按照cid、author分组,再按照id倒叙,取出前2条记录出来。        oracle里面可以通过row_number() OVER (PARTITIO...
  • mchdba
  • mchdba
  • 2014年09月10日 22:50
  • 10224

MySQL分组排序取前N条记录以及生成自动数字序列--group by 后 limit 外加 rownumber

同事提了一个需求,要求按照某列分组,然后将各组的前几条抽取出来。 表结构 CREATE TABLE `total_freq_ctrl` ( `time` int(10) unsigned NOT ...
  • ylqmf
  • ylqmf
  • 2014年09月02日 14:46
  • 27363

mysql中GROUP BY分组取前N条记录实现

mysql中GROUP BY分组取前N条记录实现 mysql分组,取记录 GROUP BY之后如何取每组的前两位下面我来讲述mysql中GROUP BY分组取前N条记录实现方法。 这是测试表...

mysql分组取每组前几条记录(排名) 附group by与order by的研究

http://www.jb51.net/article/31590.htm -按某一字段分组取最大(小)值所在行的数据  复制代码代码如下: /*  数据如下:  name ...

MYSQL group by 分组后只取最大、最小值对应的记录方法

这里有2篇文章 综合起来就差不多了 工作忙 没时间整理 其实和其它数据库一样 并没有什么捷径 效率的话还是join比较快 MYSQL每个用户取1条记录的三种写法(group...

sql获取分组后的第二条数据 group by limit

做互联网金融1年多了,每天各种统计sql的写,算投资、算留存、算转化。。。。 今天运营同事有个需求要求获取一段时间内每个用户的首投、复投,首投好算,直接groupby就是第一条投资,可获取复投的时候...

关于MYSQL group by 分组按时间取最大值的实现方法!

类如有一个帖子的回复表,posts( id , tid , subject , message , dateline ) , id 为 自动增长字段, tid为该回复的主题帖子的id(外键关联...

关于MYSQL group by 分组按时间取最大值的实现方法!

类如 有一个帖子的回复表,posts( id , tid , subject , message ,  dateline ) , id 为 自动增长字段, tid为该回复的主题帖子的id(外键关...

MySQL数据库与其他数据库的3个常用语法区别(外链接、分组排序row_number() over(partition by ) 、group by和distinct)

MySQL的小巧灵活易用性,和开源性,使得应用面非常广,但是缺点也 比较多,一些常用的sql语句也有差别。以前也遇到过,最近做项目又有体会,还是记录下来省的以后又忘记了。 1、MySQL数据库不支持最...

Oracle分组查询取每组排序后的前N条记录

项目中用到Oracle分组查询取每组排序后的前N条记录,group by 只能返回每个组的单条统计。所以用OVER(PARTITION BY)函数,具体详见baidu.com 建个测试表test_ab...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:[MySQL] 分组排序取前N条记录以及生成自动数字序列,类似group by后 limit
举报原因:
原因补充:

(最多只允许输入30个字)