[知识总结] 【leetcode】SQL编程题题解

本文将记录力扣SQL下的题目：

除了较多的连接题目以外，有很多的排序题目，需要用到窗口函数

175.组合两个表

很基本的两表关联

SELECT 
p.FirstName AS firstName, p.LastName as lastName, a.City AS city, a.State AS state
FROM Person p
LEFT JOIN Address a
ON p.PersonId = a.PersonId

176.第二高的薪水

考查LIMIT的使用，同时考虑记录只有一条的情况

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

177.第N高的薪水

这道题给了一个mysql的脚本

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N:=N-1;
  RETURN (
      # Write your MySQL query statement below.
        SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT N,1
  );
END

178. 分数排名

这道题使用窗口函数会很方便

• row_number() 在排名是序号连续、不重复，即使遇到表中的两个一样的数值亦是如此
• rank() 函数会把要求排序的值相同的归为一组且每组序号一样（序号可能会间断）
• dense_rank() 排序是连续的，也会把相同的值分为一组且每组排序号一样
• ntile(group_num) 将所有记录分成group_num个组，每组序号一样

select Score, dense_rank() over(order by Score desc) 'Rank' from Scores

179.连续出现的数字

数字连续出现三次以上，将同一张表自关联三次

SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM 
    Logs l1,Logs l2,Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND
    l2.Id = l3.Id - 1
    AND
    l1.Num = l2.Num
    AND
    l2.Num = l3.Num

181.超过经理收入的员工

自关联作比较

SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary

182.查找重复的电子邮箱

分组之后根据count判断

SELECT Email 
FROM Person
GROUP BY Email
HAVING count(Email) > 1

183.从不订购的客户

使用NOT IN

SELECT Name Customers FROM Customers WHERE Id NOT IN (SELECT CustomerId FROM Orders)

184.部门工资最高的员工

关键在于WHERE之后的限定，使用两个字段IN一个子查询（ID和最大薪资）

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId, Employee.Salary) IN
    (SELECT DepartmentId, MAX(Salary)
    FROM Employee
    GROUP BY DepartmentId)