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