mysql 分组后排序 @pre 和@rank

数据表 employee 如图

每个部门的工资排序并有名次

## 先(部门,薪水)去重,再 部门(升),薪水(降) 排序
SELECT te.DepartmentId, te.Salary,
       CASE 
            WHEN @pre = DepartmentId THEN @rank:= @rank + 1
            WHEN @pre := DepartmentId THEN @rank:= 1
       END AS RANK
FROM (SELECT @pre:=null, @rank:=0)tt,
## (部门,薪水)去重,根据 部门(升),薪水(降) 排序
     (
         SELECT DepartmentId,Salary
         FROM Employee
         GROUP BY DepartmentId,Salary
         ORDER BY DepartmentId,Salary DESC
     )te

查询结果