mysql 实现分析函数_mysql实现oracle分析函数的功能

由于MYSQL没有提供类似ORACLE中OVER()这样丰富的分析函数.

所以在MYSQL里需要实现这样的功能,我们只能用一些灵活的办法:

1.首先我们来创建实例数据:

drop table if exists heyf_t10;

create table heyf_t10 (empid int ,deptid int ,salary decimal(10,2)

);

insert into heyf_t10 values

(1,10,5500.00),

(2,10,4500.00),

(3,20,1900.00),

(4,20,4800.00),

(5,40,6500.00),

(6,40,14500.00),

(7,40,44500.00),

(8,50,6500.00),

(9,50,7500.00);

2. 确定需求: 根据部门来分组,显示各员工在部门里按薪水排名名次.

显示结果预期如下:

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

| empid | deptid | salary |

rank |

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

| 1

| 10 | 5500.00

| 1 |

| 2

| 10 | 4500.00

| 2 |

| 4

| 20 | 4800.00

| 1 |

| 3

| 20 | 1900.00

| 2 |

| 7

| 40 | 44500.00

| 1 |

| 6

| 40 | 14500.00

| 2 |

| 5

| 40 | 6500.00

| 3 |

| 9

| 50 | 7500.00

| 1 |

| 8

| 50 | 6500.00

| 2 |

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

3. SQL 实现

select empid,deptid,salary,rank from (

select

heyf_tmp.empid,heyf_tmp.deptid,heyf_tmp.salary,@rownum:=@rownum+1

,

if(@pdept=heyf_tmp.deptid,@rank:=@rank+1,@rank:=1) as rank,

@pdept:=heyf_tmp.deptid

from (

select empid,deptid,salary from heyf_t10 order by deptid asc

,salary desc

) heyf_tmp ,(select @rownum :=0 , @pdept := null ,@rank:=0) a )

result

;

4. 结果演示

mysql> select empid,deptid,salary,rank from (

-> select

heyf_tmp.empid,heyf_tmp.deptid,heyf_tmp.salary,@rownum:=@rownum+1

,

->

if(@pdept=heyf_tmp.deptid,@rank:=@rank+1,@rank:=1) as rank,

->

@pdept:=heyf_tmp.deptid

-> from

(

-> select

empid,deptid,salary from heyf_t10 order by deptid asc ,salary

desc

-> )

heyf_tmp ,(select @rownum :=0 , @pdept := null ,@rank:=0) a )

result

-> ;

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

| empid | deptid | salary |

rank |

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

| 1

| 10 | 5500.00

| 1 |

| 2

| 10 | 4500.00

| 2 |

| 4

| 20 | 4800.00

| 1 |

| 3

| 20 | 1900.00

| 2 |

| 7

| 40 | 44500.00

| 1 |

| 6

| 40 | 14500.00

| 2 |

| 5

| 40 | 6500.00

| 3 |

| 9

| 50 | 7500.00

| 1 |

| 8

| 50 | 6500.00

| 2 |

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

9 rows in set (0.00 sec)

or

SELECT

empid,deptid,salary,rank FROM

(SELECT

a.empid,@dpo:=@dp,@dp:=a.deptid AS deptid,a.salary,IF

(@dp@dpo,@id:=1,@id:=@id+1) AS rank

FROM

(SELECT * FROM heyf_t10 ORDER BY deptid ASC,salary DESC )a ,(SELECT

@id:=0,@dp:=0,@dpo:=0) b) AS a

ORDER BY

empid