Mysql implements rownum and mysqlrownum

Mysql implements rownum and mysqlrownum
Create table 'total _ freq_ctrl '(

1. 'Time' int (10) unsigned not null,
2. 'Machine 'char (64) not null,
3. 'Module' char (32) not null,
4. 'Total _ flow' int (10) unsigned not null,
5. 'Deny _ flow' int (10) unsigned not null,
6. Primary key ('module ', 'machine', 'time ')
7. ) ENGINE = InnoDB default charset = utf8

 

Original SQL

1. SELECT machine, deny_flow, total_flow, time FROM total_freq_ctrl a where 1> (select count (machine) FROM total_freq_ctrl WHERE machine =. machine AND time>. time) and. module = 'all' order by. 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.

 

1. 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;
2. Query OK, 0 rows affected (0.00 sec)
4. Query OK, 0 rows affected (0.00 sec)
6. + -------- + --------------- + ------------ + -------- +
7. | Module | machine | time | rownum |
8. + -------- + --------------- + ------------ + -------- +
9. | All | 10.201.20.181 | 1409640060 | 1 |
10. | All | 10.201.20.181 | 1409640000 | 2 |
11. | All | 10.201.20.181 | 1409639940 | 3 |
12. | All | 10.201.20.181 | 1409639880 | 4 |
13. | All | 10.201.000097 | 1409640060 | 5 |
14. | All | 10.201.000097 | 1409640000 | 6 |
15. | All | 10.201.000097 | 1409639940 | 7 |
16. | All | 10.201.000097 | 1409639880 | 8 |
17. | All | 10.201.000098 | 1409640060 | 9 |
18. | All | 10.201.000098 | 1409640000 | 10 |
19. + -------- + --------------- + ------------ + -------- +

Rownumber already exists. Add @ mid to group

 

 

1. 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;
2. Query OK, 0 rows affected (0.00 sec)
4. Query OK, 0 rows affected (0.00 sec)
6. + -------- + --------------- + ------------ + -------- + --------------- +
7. | Module | machine | time | rownum | @ mid: = machine |
8. + -------- + --------------- + ------------ + -------- + --------------- +
9. | All | 10.201.20.181 | 1409640180 | 1 | 10.201.20.181 |
10. | All | 10.201.20.181 | 1409640120 | 2 | 10.201.20.181 |
11. | All | 10.201.20.181 | 1409640060 | 3 | 10.201.20.181 |
12. | All | 10.201.20.181 | 1409640000 | 4 | 10.201.20.181 |
13. | All | 10.201.20.181 | 1409639940 | 5 | 10.201.20.181 |
14. | All | 10.201.20.181 | 1409639880 | 6 | 10.201.20.181 |
15. | All | 10.201.20.97 | 1409640180 | 1 | 10.201.000097 |
16. | All | 10.201.20.97 | 1409640120 | 2 | 10.201.000097 |
17. | All | 10.201.20.97 | 1409640060 | 3 | 10.201.20.97 |
18. | All | 10.201.20.97 | 1409640000 | 4 | 10.201.000097 |
19. | All | 10.201.20.97 | 1409639940 | 5 | 10.201.000097 |
20. | All | 10.201.20.97 | 1409639880 | 6 | 10.201.000097 |
21. | All | 10.201.20.98 | 1409640180 | 1 | 10.201.000098 |
22. | All | 10.201.20.98 | 1409640120 | 2 | 10.201.000098 |
23. | All | 10.201.000098 | 1409640060 | 3 | 10.201.000098 |
24. | All | 10.201.20.98 | 1409640000 | 4 | 10.201.000098 |
25. | All | 10.201.20.98 | 1409639940 | 5 | 10.201.000098 |
26. | All | 10.201.000098 | 1409639880 | 6 | 10.201.000098 |
27. + -------- + --------------- + ------------ + -------- + --------------- +

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

 

1. Set @ row = 0; set @ mid = ''; select. *, 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 =. module and B. machine =. machine and B. time =. time where B. rownum <5;
2. Query OK, 0 rows affected (0.00 sec)
4. Query OK, 0 rows affected (0.00 sec)
6. + ------------ + --------------- + -------- + ------------ + ----------- + -------- +
7. | Time | machine | module | total_flow | deny_flow | rownum |
8. + ------------ + --------------- + -------- + ------------ + ----------- + -------- +
9. | 1409640360 | 10.201.20.181 | all | 53937 | 6058 | 1 |
10. | 1409640300 | 10.201.20.181 | all | 52588 | 5701 | 2 |
11. | 1409640240 | 10.201.20.181 | all | 54254 | 5608 | 3 |
12. | 1409640180 | 10.201.20.181 | all | 54684 | 5811 | 4 |
13. | 1409640360 | 10.201.000097 | all | 50679 | 5307 | 1 |
14. | 1409640300 | 10.201.000097 | all | 50472 | 5239 | 2 |
15. | 1409640240 | 10.201.000097 | all | 51586 | 5509 | 3 |
16. | 1409640180 | 10.201.000097 | all | 50794 | 5378 | 4 |
17. | 1409640360 | 10.201.000098 | all | 84747 | 5652 | 1 |
18. | 1409640300 | 10.201.000098 | all | 84506 | 5696 | 2 |
19. | 1409640240 | 10.201.000098 | all | 84982 | 5513 | 3 |
20. | 1409640180 | 10.201.000098 | all | 83997 | 5623 | 4 |
21. + ------------ + --------------- + -------- + ------------ + ----------- + -------- +