MySQL group sort takes the first N records and generates an automatic number sequence--group by limit plus RowNumber

A colleague asked for a request, grouped by a column, and then extracted the first few of the groups.

Table structure

CREATE TABLE ' Total_freq_ctrl ' (  ' time ' int () unsigned not NULL,  ' machine ' char (+) ' NOT NULL,  ' module ' char (+) Not NULL,  ' total_flow ' int (ten) unsigned not NULL,  ' deny_flow ' int (ten) unsigned not NULL,  PRIMARY KEY (' Mo Dule ', ' machine ', ' time ') Engine=innodb DEFAULT Charset=utf8

[Email protected]

Original SQL

Select Machine, Deny_flow, Total_flow, time from Total_freq_ctrl A WHERE 1 > (SELECT COUNT (machine) from total_freq_ctr L WHERE machine = A.machine and Time > A.time) and A.module = "All" ORDER by a.time Desc;

Just change the 1 to N to take the first n of each group, because I don't like subqueries very much, and I try to change the way I'm trying to rename a join.

However, all of the data needs to be sorted to determine the first n of each group, so the best optimization is also scanned all the way through the table.

First I want to sort the data in the table, introduce a variable @row to do RowNumber

Set @row =0;set @mid = ";  SELECT module, machine, Time, @row: [e-mail protected]+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 | Ten |+--------+---------------+------------+--------+

RowNumber has come out, add a @mid to group

Set @row =0;set @mid = "; SELECT module, machine, time,case if @mid = Machine then @row: [email protected]+1 else @row: =1 end RowNum, @mid: =ma Chine 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 |+--------+---------------+------------+--------+---------------+

Well, then add a layer of inner join and then limit the RowNumber to get the target data.

[Email protected]

Set @row =0;set @mid = "; Select A.*,b.rownum from Total_freq_ctrl a INNER join (select module, machine, time, case time @mid = Machine then @row: [email protected]+1 else @row: =1 end RowNum, @mid: =machine mid from Total_freq_ctrl order by MoD Ule,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 classmates recommended this link: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

MySQL group sort takes the first N records and generates an automatic number sequence--group by limit plus RowNumber