解决方案：
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 |  
-----------------------------------------------

做好的sql,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 ; 
结果演示 
+-------+--------+----------+------+ 
| 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) 
详解：
执行顺序：FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT
特别主要需要将去重的记录通过order by 汇总到一起