本文共 1918 字，大约阅读时间需要 6 分钟。

题目叙述

要求

select d.name department,a.name name,a.salary from department d,(SELECT name,salary ,departmentid,Dense_rank() over(partition by departmentid order by salary desc) as ranking from  employee1 ) a where a.departmentid = d.id and a.ranking <= 3

此方法最为简单，看着不复杂，在此不做详细解释

SELECT dep.Name Department, emp.Name Employee, emp.SalaryFROM Employee empINNER JOIN Department dep ON emp.DepartmentId = dep.IdORDER BY emp.DepartmentId, emp.Salary DESC

2.每个部门的员工根据薪水进行排序

由于原本没有排序的字段,所以这里就需要自定义变量补充一个字段出来

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

组合步骤

SELECT dep.Name Department, emp.Name Employee, emp.SalaryFROM (## 自定义变量RANK, 查找出 每个部门工资前三的排名        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       )tINNER JOIN Department dep ON t.DepartmentId = dep.IdINNER JOIN Employee emp ON t.DepartmentId = emp.DepartmentId and t.Salary = emp.Salary and t.RANK <= 3ORDER BY t.DepartmentId, t.Salary DESC ## t 结果集已有序,根据该集合排序

转载地址：http://fzgzi.baihongyu.com/