mysql分析函数的实现（阿里DBA博客，大开眼界）

来源：互联网发布：淘宝爱互动编辑：程序博客网时间：2024/05/17 04:44

今天收到同事的一需求，要求实现以下功能:

drop table test;

create table test(name varchar(10),name1 varchar(10),count bigint);

delete from test;

insert into test values(’1′,’a',2);

insert into test values(’1′,’b',1);;

insert into test values(’1′,’c',4);

insert into test values(’1′,’d',5);

insert into test values(’1′,’e',7);

insert into test values(’1′,’f',8);

insert into test values(’2′,’g',9);

insert into test values(’2′,’h',0);

insert into test values(’2′,’i',21);

insert into test values(’2′,’j',3);

insert into test values(’2′,’k',4);

insert into test values(’2′,’l',56);

insert into test values(’3′,’m',67);

insert into test values(’3′,’n',89);

insert into test values(’3′,’o',12);

insert into test values(’3′,’p',22);

insert into test values(’3′,’q',23);

insert into test values(’3′,’r',42);

insert into test values(’3′,’s',26);

根据name字段分组，取出改组内的前4项，并且按照count字段进行降序排序，由于mysql没有oracle中的分析函数，看上去很简单的需求，但是折腾了许久，还是没有实现，于是乎在网上收罗了一下mysql分析函数是怎么实现的，找到了mysql分析函数的解决办法，学习了一下，于是乎把同事的功能实现了；

select name, name1, count  from (select b.name, b.name1, b.count,if(@name = b.name, @rank := @rank + 1, @rank := 1) as rank,@name:=b.namefrom (select name, name1, count    from test order by name asc, count desc) b,(select @rownum := 0, @name := null, @rank := 0) a) result where rank<5;

结果：

| name | name1 | count |

+——+——-+——-+

| 1 | f | 8 |

| 1 | e | 7 |

| 1 | d | 5 |

| 1 | c | 4 |

| 2 | l | 56 |

| 2 | i | 21 |

| 2 | g | 9 |

| 2 | k | 4 |

| 3 | n | 89 |

| 3 | m | 67 |

| 3 | r | 42 |

| 3 | s | 26 |

+——+——-+——-+

12 rows in set (0.02 sec)

如果上面的sql初次看到有些让人摸不着头脑的话，你可以看看他的执行计划,然后从执行计划得到一些执行流程，该sql中最核心的技术点为使用自定义变量来保存sql执行过程中的值：

if(@name = b.name, @rank := @rank + 1, @rank := 1) as rank,@name:=b.name

这条判断语句对下面结果进行处理，并生成rank，由于下面查询的结果中对name做了排序，所以@name:=b.name使的相同name值的rank能够递增；

+—-+————-+————+——–+—————+——+———+——+——+—————-+

+—-+————-+————+——–+—————+——+———+——+——+—————-+

如果你对下面的select @rownum := 0, @name := null, @rank := 0看不太明白，可以改写一下sql：

select name, name1, count from (select b.name, b.name1,b.count,if(@name = b.name, @rank := @rank + 1, @rank := 1) as rank,@name:=b.namefrom (select name, name1, count,@rownum := 0, @name := null, @rank := 0from test order by name asc, count desc) b) result where rank<4;

结果：

+—-+————-+————+——+—————+——+———+——+——+—————-+

+—-+————-+————+——+—————+——+———+——+——+—————-+

+—-+————-+————+——+—————+——+———+——+——+—————-+

转载自：http://hidba.org/?p=295

0 0