mysql实现类似rownumber()的效果

 

--Create test table
create table tmp_test(empid int ,deptid int ,salary decimal(10,2) );

--Insert test data
insert into tmp_test
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),
(10,10,4800.00),
(11,10,5800.00);

--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 tmp_test order by deptid asc ,salary desc
) heyf_tmp ,(select @rownum :=0 , @pdept := null ,@rank:=0) a ) result
where rank<=3