请哪位高手帮忙将下面oracle中的分组排序的sql代码转化为mysql中的代码？

Question

select a.*, row_number() over (partition by aaa order by bbb desc) as group_idxfrom table1 aorder by aaa

Answer 1

案例一个　
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);

显示结果预期如下:
　　+-------+--------+----------+------+
　　| 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 |
　　+-------+--------+----------+------+

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
　　;

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)