[MySQL] 分组排序取前N条记录以及生成自动数字序

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

前言：

        同事的业务场景是，按照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. 
   
9. 
   
10. INSERT INTO test VALUES 
    
11. (1,1,\'test1\'),
    
12. (2,1,\'test1\'),
    
13. (3,1,\'test2\'),
    
14. (4,1,\'test2\'),
    
15. (5,1,\'test2\'),
    
16. (6,1,\'test3\'),
    
17. (7,1,\'test3\'),
    
18. (8,1,\'test3\'),
    
19. (9,1,\'test3\'),
    
20. (10,2,\'test11\'),
    
21. (11,2,\'test11\'),
    
22. (12,2,\'test22\'),
    
23. (13,2,\'test22\'),
    
24. (14,2,\'test22\'),
    
25. (15,2,\'test33\'),
    
26. (16,2,\'test33\'),
    
27. (17,2,\'test33\'),
    
28. (18,2,\'test33\');
    
29. 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. 
    
27. 
    
28. 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. 
   
4. 
   
5. mysql> SET @mid=\'\';
   
6. QUERY OK, 0 ROWS affected (0.00 sec)
   
7. 
   
8. 
   
9. mysql> SELECT a.*,b.rownum FROM test a 
   
10.     -> INNER JOIN (
    
11.     -> SELECT cid, author, id, CASE WHEN @mid = author THEN @row:=@row+1 ELSE @row:=1 END rownum, @mid:=author MID 
    
12.     -> FROM test 
    
13.     -> ORDER BY cid,author,id DESC
    
14.     -> ) b ON b.author=a.author AND b.cid=a.cid AND b.id=a.id WHERE b.rownum<3; 
    
15. +-----+------+----------------+--------+
    
16. | id | cid | author | rownum |
    
17. +-----+------+----------------+--------+
    
18. | 2 | 1 | test1 | 1 |
    
19. | 1 | 1 | test1 | 2 |
    
20. | 5 | 1 | test2 | 1 |
    
21. | 4 | 1 | test2 | 2 |
    
22. | 9 | 1 | test3 | 1 |
    
23. | 8 | 1 | test3 | 2 |
    
24. | 11 | 2 | test11 | 1 |
    
25. | 10 | 2 | test11 | 2 |
    
26. | 14 | 2 | test22 | 1 |
    
27. | 13 | 2 | test22 | 2 |
    
28. | 18 | 2 | test33 | 1 |
    
29. | 17 | 2 | test33 | 2 |
    
30. | 200 | 200 | 200test_nagios | 1 |
    
31. +-----+------+----------------+--------+
    
32. 13 ROWS IN SET (0.01 sec)
    
33. 
    
34. 
    
35. mysql>

参考文章地址：
http://blog.csdn.net/mchdba/article/details/22163223
http://blog.csdn.net/ylqmf/article/details/39005949

可以写为 select id,channel_id,time from table where group by channel_id order by time desc limit 2
 

# mysql不支持其它复杂数据库的类似 rank() over 的排名和统计查询
# 只能通过变通的子查询和逻辑计算方式来实现，对于中小数据量可以考虑

-- rank 排名实现
select inline_rownum, aa, cc, amt, orderid FROM
(
select
# logic_cal 只是实现计数器计算的，每次逐条查询时会对比当前 cc 与 @last_cc 是否相同，如果不同则把当前该列值赋于 @last_cc 并重设计数器 @num := 1，否则计数器自加 @num := @num + 1
(case when cc <> @last_cc then concat(@last_cc := cc, @num := 1 ) else concat(@last_cc, @num := @num + 1) end ) logic_cal
, @num as inline_rownum
, aa, cc, amt, orderid
from tb_rank,
( select @last_cc := '') t, # 初始化 @last_cc 为 '', 如要检查的列(基于计数器统计的列)是int型，则初始化为0; varchar型初始化为''
( select @num := 0 ) t2 # 初始化@num为0
order by cc, orderid asc # 排序的方式会影响@num的生成，因为logic_cal是逐行计算的
) t
where inline_rownum <= floor(amt*0.8) #限制条数，取常量值或其他
order by cc,orderid asc
;