获取第二高的薪水
select ifnull((select distinct Salary from Employee order by Salary desc limit 1,1),null ) as SecondHighestSalary;
获取第N高的薪水
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N=N-1;
RETURN (
select ifnull((select distinct Salary from Employee order by Salary desc limit N,1),null) as SecondHighestSalary
);
END;
分数排名
SELECT Score, (SELECT count(DISTINCT score) FROM Scores WHERE score >= s.score) AS Rank FROM Scores s ORDER BY Score DESC ;
获取部门前3高的薪水 （分组排序取每组前3）
select
b.name as Department,
a.name as Employee,
a.Salary
from employee a left join department b on a.departmentid = b.id
where
(select
count(distinct salary)
from employee
where a.departmentid = departmentid and Salary> a.Salary) < 3
and b.name is not null
order by a.departmentid,a.Salary DESC;
获取部门的最高薪水（分组排序取1）
1、
select a.Department,a.Employee,a.Salary from
(select d.Name as Department, e.name as Employee ,e.Salary,e.DepartmentId
from employee e left join department d on e.DepartmentId=d.Id order by e.Salary desc)
as a group by a.DepartmentId;
2、
SELECT d.Name AS Department, e.Name AS Employee, e.Salary FROM
Employee e, Department d WHERE e.DepartmentId=d.Id AND e.Salary=(SELECT MAX(Salary) FROM Employee WHERE DepartmentId=d.Id);
用到的表
