A colleague's business scenario is to group by cid and author, and then reverse the id to retrieve the first two records. In oracle, row_number () OVER (PARTITIONBYcid, authorORDERBYidDESC) can be used to group data by cid and author.
Preface:
The business scenario of a colleague is to group by cid and author, and then reverse the id to retrieve the first two records.
In oracle, row_number () OVER (partition by cid, author order by id DESC) can be used to indicate the group based on cid and author, and the group is sorted BY id within the group, the value calculated by this function indicates the sequential numbers after sorting in each group (the sequential and unique in the group), and the mysql database does not have such a statistical function, you need to write complex SQL statements for implementation.
1. input test data
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 valuees (200,200, \ '200test _ nagios \');
2. original low-efficiency subquery implementation methods
SELECT * FROM test
WHERE
N> (
Select count (*)
FROM test B
WHERE a. cid = B. cid AND a. 'author' = B. 'author' AND a. id ) Order by cid, author, id DESC;
If you replace N with a number such as 2, the first two records of each group are queried, as shown below:
Mysql> SELECT * FROM test
-> WHERE
-> 2> (
-> Select count (*)
-> FROM test B
-> WHERE a. cid = B. cid AND a. 'author' = B. 'author' AND a. 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. use dynamic SQL
First, construct the serial number and introduce a @ row for rownumber.
SET @ row = 0; SET @ mid = ''; SELECT cid, author, @ row: = @ row + 1 rownum FROM test order by cid, author LIMIT 10;
The serial number has come out, and an additional @ mid is added for grouping, with the focus on case when @ mid = author THEN @ row: = @ row + 1 ELSE @ row: = 1 END rownum, indicates that the group data is automatically traversed from 1 count.
Now, add an inner JOIN to the table and then limit rownumber to get the target data.
SET @ row = 0;
The execution result is as follows:
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
-> 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>
Reference article address:
Http://blog.csdn.net/mchdba/article/details/22163223
