mysql·Ö×éÅÅÐò·½°¸

mssql,oracleÖÐ
test񡜧
1 5 abc
2 6 bcd
1 7 ade
2 8 adc
select   a,b,c
from(
select   a,b,c
,row_number()over(partition   by   a   order   by   b   desc)   rn
from   test
)  
where   rn=1
ÓÉÓÚMYSQLûÓÐÌṩÀàËÆORACLEÖÐOVER()ÕâÑù·á¸»µÄ·ÖÎöº¯Êý. ËùÒÔÔÚMYSQLÀïÐèҪʵÏÖÕâÑùµÄ¹¦ÄÜ,ÎÒÃÇÖ»ÄÜÓÃһЩÁé»îµÄ°ì·¨:
Àý1
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 , @p