前言：

同事的業務場景是，按照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;

執行結果如下所示：

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] 分組排序取前N條記錄以及生成自動數字序列，類似group by後 limit

相關文章