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