关闭

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

475人阅读 评论(0) 收藏 举报
分类:
前言:

        同事的业务场景是,按照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>
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:299278次
    • 积分:5198
    • 等级:
    • 排名:第5284名
    • 原创:187篇
    • 转载:316篇
    • 译文:0篇
    • 评论:4条
    最新评论