首页 > 代码库 > MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序功能
MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序功能
MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序功能
由于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 |+-------+--------+----------+------+9 rows in set (0.00 sec)
3. SQL 实现
SELECT empid, deptid, salary, rankFROM ( SELECT empid, deptid, salary, IF ( @pdept = src.deptid ,@rank := @rank + 1 ,@rank := 1 ) AS rank, @pdept := src.deptid AS g FROM ( SELECT empid, deptid, salary FROM heyf_t10 ORDER BY deptid ASC, salary DESC ) src, ( SELECT @pdept := NULL ,@rank := 0 ) var ) z;
4. 结果演示
mysql> SELECT -> empid, -> deptid, -> salary, -> rank -> FROM -> ( -> SELECT -> empid, -> deptid, -> salary, -> -> IF ( -> @pdept = src.deptid ,@rank := @rank + 1 ,@rank := 1 -> ) AS rank, -> @pdept := src.deptid AS g -> FROM -> ( -> SELECT -> empid, -> deptid, -> salary -> FROM -> heyf_t10 -> ORDER BY -> deptid ASC, -> salary DESC -> ) src, -> ( -> SELECT -> @pdept := NULL ,@rank := 0 -> ) var -> ) z;+-------+--------+----------+------+| 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)
转自: http://ace105.blog.51cto.com/639741/724411
MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序功能
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。