Mysql：实现row_number分组排序功能

 

在sql server 和 oracle 中均有row_number 实现功能，即对查询结果进行分组排序添加字段。而在mysql中无内置函数，需要曲线救国。

表结构：

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

 

1、通过内表连接进行对应字段大小计数方式判断该条记录所处的row_number

 

SELECT machine, deny_flow, total_flow, time

FROM total_freq_ctrl A

WHERE ( SELECT COUNT(machine)

        FROM total_freq_ctrl

        WHERE machine = A.machine AND time > A.time) < 1

     AND A.module = 'all'

ORDER BY A.time desc;

  在修改排序序号的位置，修改你需要取出的序列号，即为取出N-1的序号

2、引入@rownum 将表内数据添加序列号

set @row=0;

SELECT module, machine, time, @row:=@row+1 rownum

FROM total_freq_ctrl

order by module,machine,time desc

limit 10;

 

结果展示：

+--------+---------------+------------+--------+

| 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 |

+--------+---------------+------------+--------+

 

3、添加@mid来进行分组，按mid进行分组添加rownum

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;

 

结果展示：

+--------+---------------+------------+--------+---------------+

| 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  |

+--------+---------------+------------+--------+---------------+

 

注：1、Mysql中添加rownum功能，主要是group by变量改变，设置order by 排序进行rownum增加。再根据子查询，join，having 等条件进行对rownum筛选。

2、若只是取出前几条而不添加rownum字段值，可以直接进行内连接表，count内表值order by外表值的条数来进行控制选出的rownum。

3、若只是简单的排除数据可以利用exists,not exists,join ,in条件等。

 

注：这个用了几次发现应该注意的问题：

 

1、为什么没有分类排序？排序总是1等

     可能是排序的group by变量没有设置正确，没有初始赋值set @mid=''语句，变量设置在判断条件之前进行了赋值操作，即@mid:=machine一定要在case when之后。

 

2、为什么排序的结果不是安装分组的顺序，总是1或者随机的等？

    可能在排序的结果集中，你只是添加了order by的排序字段，但是没有将group by变量添加到order by里面。其中我想mysql是不断的对@mid:=machine的赋值来进行排序，那么一定要让数据先按照分组并排序好的状态下才能添加正确的id。如果没有对分组字段排序，就等于检索的结果是不确定的。