[sql] 经典的四道sql面试题目

1、查找工资比经理高的员工leetcode181

这道题用到了分表查询的思想，将一个表分成两个子表再连接起来，之后再对比同一个记录的两项的思想。

SELECT 
    a.name as Employee from employee as a
    inner join employee as b on a.managerId=b.id and a.salary>b.salary

也可以直接写where语句 两个记录的id相同且薪资较大

SELECT 
    a.name as Employee from employee as a,employee as b
where a.managerId=b.id and a.salary>b.salary

2、查询每个部门公资最高的员工leetcode 184

这题用分组查询，可以把分组查询的结果每个课程的最高分及课程号，作为一个新的表加到from中作为查询的条件，比如where需要同时满足这两个条件时不好书写，则可以用and直接匹配新表

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
         JOIN
    Department  ON Employee.DepartmentId = Department.Id ,
    (SELECT
            DepartmentId, MAX(Salary) As 'm'
        FROM
            Employee
        GROUP BY DepartmentId) as b
where
    Employee.DepartmentId=b.DepartmentId and Employee.Salary=b.m

3、leetcode 176 查询工资第二高

dense_rank会把重复的算做一个1223 rank算完重复的会跳跃 1224 ROW_NUMBER()就是每条数据都加序号，这里根据题目要求用dense_rank

select ifnull(
     (select distinct(a.salary)  from employee as a 
inner join(select salary,dense_rank() over(order by salary desc) as 'r' from employee) as b
on a.salary=b.salary
where b.r=2)
,NULL)as SecondHighestSalary

也可以排序后取数使用 limit 1 offset 1; //从Employee（员工）表中，读取第二高的salary（薪水），排序后，跳过第一个下标，读取一个元素

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary

4、leetcode 185 部门工资前三高的员工

设置一个记录a，a输出部门工资前三高的员工，这意味着工资比a大的员工不超过3个，求解一个计数，在课程号一致的前提下，计数b.alary>a,salary 这个结果的count 小于3

# Write your MySQL query statement below
select d.name as Department 
,a.name as Employee
,a.salary as Salary
from Employee as a join Department as d on a.Departmentid=d.id 
where 3>(select count(distinct b.salary) from Employee as b where a.Departmentid=b.Departmentid and b.salary>a.salary)
order by d.id and a.salary desc

这里count distinct把重复值算作一个