记录几条有用的sql

获取第二高的薪水

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);

用到的表