Mysql：实现row_number分组排序功能

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。如果没有对分组字段排序，就等于检索的结果是不确定的。