MySQL grouping and sorting takes the first N records and generates an automatic number sequence -- group by after limit plus rownumber, -- grouprownumber

MySQL grouping and sorting takes the first N records and generates an automatic number sequence -- group by after limit plus rownumber, -- grouprownumber

My colleague asked me to group by column and then extract the first few items from each group.

Table Structure

CREATE TABLE `total_freq_ctrl` (  `time` int(10) unsigned NOT NULL,  `machine` char(64) NOT NULL,  `module` char(32) NOT NULL,  `total_flow` int(10) unsigned NOT NULL,  `deny_flow` int(10) unsigned NOT NULL,  PRIMARY KEY (`module`,`machine`,`time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Tudou@ B2C .xiaomi.com

Original SQL

SELECT machine, deny_flow, total_flow, time FROM total_freq_ctrl A WHERE 1 > (SELECT COUNT(machine) FROM total_freq_ctrl WHERE machine = A.machine AND time > A.time) AND A.module = 'all' ORDER BY A.time desc;

Change 1 to N to get the first N records of each group. Because I do not like subqueries very much, I will try to rename the join method.

However, we need to sort all the data to determine the first N entries in each group. Therefore, we need to perform a full table scan for the Best optimization.

First, I want to sort the data in the table and introduce a variable @ row for rownumber.

set @row=0;set @mid='';SELECT module, machine, time, @row:=@row+1 rownum FROM total_freq_ctrl order by module,machine,time desc limit 10; Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)+--------+---------------+------------+--------+| module | machine       | time       | rownum |+--------+---------------+------------+--------+| all    | 10.201.20.181 | 1409640060 |      1 || all    | 10.201.20.181 | 1409640000 |      2 || all    | 10.201.20.181 | 1409639940 |      3 || all    | 10.201.20.181 | 1409639880 |      4 || all    | 10.201.20.97  | 1409640060 |      5 || all    | 10.201.20.97  | 1409640000 |      6 || all    | 10.201.20.97  | 1409639940 |      7 || all    | 10.201.20.97  | 1409639880 |      8 || all    | 10.201.20.98  | 1409640060 |      9 || all    | 10.201.20.98  | 1409640000 |     10 |+--------+---------------+------------+--------+

Rownumber already exists. Add @ mid to group

set @row=0;set @mid='';SELECT module, machine, time,case when @mid = machine then @row:=@row+1 else @row:=1 end rownum, @mid:=machine FROM total_freq_ctrl order by module,machine,time desc limit 20;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)+--------+---------------+------------+--------+---------------+| module | machine       | time       | rownum | @mid:=machine |+--------+---------------+------------+--------+---------------+| all    | 10.201.20.181 | 1409640180 |      1 | 10.201.20.181 || all    | 10.201.20.181 | 1409640120 |      2 | 10.201.20.181 || all    | 10.201.20.181 | 1409640060 |      3 | 10.201.20.181 || all    | 10.201.20.181 | 1409640000 |      4 | 10.201.20.181 || all    | 10.201.20.181 | 1409639940 |      5 | 10.201.20.181 || all    | 10.201.20.181 | 1409639880 |      6 | 10.201.20.181 || all    | 10.201.20.97  | 1409640180 |      1 | 10.201.20.97  || all    | 10.201.20.97  | 1409640120 |      2 | 10.201.20.97  || all    | 10.201.20.97  | 1409640060 |      3 | 10.201.20.97  || all    | 10.201.20.97  | 1409640000 |      4 | 10.201.20.97  || all    | 10.201.20.97  | 1409639940 |      5 | 10.201.20.97  || all    | 10.201.20.97  | 1409639880 |      6 | 10.201.20.97  || all    | 10.201.20.98  | 1409640180 |      1 | 10.201.20.98  || all    | 10.201.20.98  | 1409640120 |      2 | 10.201.20.98  || all    | 10.201.20.98  | 1409640060 |      3 | 10.201.20.98  || all    | 10.201.20.98  | 1409640000 |      4 | 10.201.20.98  || all    | 10.201.20.98  | 1409639940 |      5 | 10.201.20.98  || all    | 10.201.20.98  | 1409639880 |      6 | 10.201.20.98  |+--------+---------------+------------+--------+---------------+

Now, add an inner join to the table and then limit rownumber to get the target data.

Tudou@ B2C .xiaomi.com

set @row=0;set @mid='';select a.*,b.rownum from total_freq_ctrl a inner join (SELECT module, machine, time, case when @mid = machine then @row:=@row+1 else @row:=1 end rownum, @mid:=machine mid FROM total_freq_ctrl order by module,machine,time desc) b on b.module=a.module and b.machine=a.machine and b.time=a.time where b.rownum<5;Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)+------------+---------------+--------+------------+-----------+--------+| time       | machine       | module | total_flow | deny_flow | rownum |+------------+---------------+--------+------------+-----------+--------+| 1409640360 | 10.201.20.181 | all    |      53937 |      6058 |      1 || 1409640300 | 10.201.20.181 | all    |      52588 |      5701 |      2 || 1409640240 | 10.201.20.181 | all    |      54254 |      5608 |      3 || 1409640180 | 10.201.20.181 | all    |      54684 |      5811 |      4 || 1409640360 | 10.201.20.97  | all    |      50679 |      5307 |      1 || 1409640300 | 10.201.20.97  | all    |      50472 |      5239 |      2 || 1409640240 | 10.201.20.97  | all    |      51586 |      5509 |      3 || 1409640180 | 10.201.20.97  | all    |      50794 |      5378 |      4 || 1409640360 | 10.201.20.98  | all    |      84747 |      5652 |      1 || 1409640300 | 10.201.20.98  | all    |      84506 |      5696 |      2 || 1409640240 | 10.201.20.98  | all    |      84982 |      5513 |      3 || 1409640180 | 10.201.20.98  | all    |      83997 |      5623 |      4 |+------------+---------------+--------+------------+-----------+--------+

Some recommended this link: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

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
;

After mysql multi-Table query, group by group order by cannot be sorted by date

Your SQL statement itself has problems. If you group, all the fields you display must be placed in the group;
After the SQL statement is correct, it is correct to sort by time in descending order