[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排序，而此Function Compute的值就表示每組內部排序後的順序編號（組內連續的唯一的)，而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;
執行結果如下所示：
- mysql> SET @row=0;
- QUERY OK, 0 ROWS affected (0.00 sec)
- mysql> SET @mid=\'\';
- QUERY OK, 0 ROWS affected (0.00 sec)
- mysql> 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;
- +-----+------+----------------+--------+
- | id | cid | author | rownum |
- +-----+------+----------------+--------+
- | 2 | 1 | test1 | 1 |
- | 1 | 1 | test1 | 2 |
- | 5 | 1 | test2 | 1 |
- | 4 | 1 | test2 | 2 |
- | 9 | 1 | test3 | 1 |
- | 8 | 1 | test3 | 2 |
- | 11 | 2 | test11 | 1 |
- | 10 | 2 | test11 | 2 |
- | 14 | 2 | test22 | 1 |
- | 13 | 2 | test22 | 2 |
- | 18 | 2 | test33 | 1 |
- | 17 | 2 | test33 | 2 |
- | 200 | 200 | 200test_nagios | 1 |
- +-----+------+----------------+--------+
- 13 ROWS IN SET (0.01 sec)
- mysql>
參考文章地址：
http://blog.csdn.net/mchdba/article/details/22163223
http://blog.csdn.net/ylqmf/article/details/39005949
mysql分組 排序 取前2條
可以寫為 select id,channel_id,time from table where group by channel_id order by time desc limit 2
mysql分組排序後取出幾條記錄，
# 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
;
