[MySQL] grouping and sorting: obtain the first N records and generate an automatic number sequence, similar to group by after limit, mysqlgroup
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
The SQL code is as follows:
- SELECT * FROM test
- 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;
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 <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. 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.
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;
Now, add an inner JOIN to the table and then limit rownumber to get the target data.
SET @ row = 0;
SET @ mid = '';
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;
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
Http://blog.csdn.net/ylqmf/article/details/39005949
Sort the first two mysql groups
It can be written as select id, channel_id, time from table where group by channel_id order by time desc limit 2
Several records are retrieved after the mysql group is sorted,
# Mysql does not support ranking and statistical queries similar to rank () over in other complex databases
# It can only be implemented through the flexible subquery and logical computing methods, and can be considered for small data volumes
-- Rank ranking implementation
Select inline_rownum, aa, cc, amt, orderid FROM
(
Select
# Logic_cal only implements counter calculation. Each query is performed one by one to compare whether the current cc and @ last_cc are the same. If they are different, the current column value is assigned to @ last_cc and the counter is reset @ num: = 1, otherwise the counter is automatically added @ 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, # initialize @ last_cc to''. If the column to be checked (the column based on counter statistics) is int type, it is initialized to 0; varchar type is initialized''
(Select @ num: = 0) t2 # initialization @ num is 0
Order by cc, orderid asc # sorting method will affect @ num generation, because logic_cal is calculated row by row.
) T
Where inline_rownum <= floor (amt * 0.8) # limit the number of entries, take the constant value or other
Order by cc, orderid asc
;
