力扣SQL刷题总结

1. 多表查询用到的联结：

   • 内联结（INNER JOIN）：取两表的公共数据；
   • 左联结（LEFT JOIN）：联结结果保留左表的全部数据；
   • 右联结（RIGHT JOIN）：联结结果保留右表的全部数据；

2. 查询第二高的薪水

-- 方法一：使用子查询和LIMIT子句
SELECT 
	(SELECT DISTINCT 
     	Salary
    FROM 
     	Employee
    ORDER BY Salary DESC
    LIMIT 1 OFFSET 1) AS SecondHighestSalary;
 
 -- (SELECT NULL返回NULL值)
 
 -- 方法二：使用IFNULL和LIMIT子句
 SELECT
 	IFNULL(
        (SELECT DISTINCT 
            Salary
        FROM 
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),NULL) AS SecondHighestSalary;

-- LIMIT y OFFSET x 分句表示查询结果跳过x条数据，读取前y条数据，相当于LIMIT x,y

3. 排名问题：查询第N高的数据

 -- 方法一:单表查询
 -- 同薪同名且不跳级：解决办法是用GROUP BY按薪水分组后再ORDER BY
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
      SELECT 
            salary
      FROM 
            employee
      GROUP BY 
            salary
      ORDER BY 
            salary DESC
      LIMIT N, 1
  );
END

-- 方法二：子查询
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      SELECT 
          DISTINCT e.salary
      FROM 
          employee e
      WHERE 
          (SELECT COUNT(DISTINCT salary) FROM employee WHERE salary > e.salary) = N-1
  );
END

-- 方法三：自连接（一般来说，能用子查询解决的问题也能用连接解决）
-- 擅用GROUP BY
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      SELECT 
          e1.salary
      FROM 
          employee e1 JOIN employee e2 ON e1.salary <= e2.salary
      GROUP BY 
          e1.salary
      HAVING 
          count(DISTINCT e2.salary) = N
  );
END

-- 方法四：笛卡尔积
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      SELECT 
          e1.salary
      FROM 
          employee e1, employee e2 
      WHERE 
          e1.salary <= e2.salary
      GROUP BY 
          e1.salary
      HAVING 
          count(DISTINCT e2.salary) = N
  );
END

-- 方法五：窗口函数
/*
1. row_number(): 同薪不同名，相当于行号 （1、2、3、4）
2. rank(): 同薪同名，有跳级 （1、2、2、4）
3. dense_rank(): 同薪同名，无跳级 （1、2、2、3）
*/
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
        SELECT 
            DISTINCT salary
        FROM 
            (SELECT salary, 
             	dense_rank() over(ORDER BY salary DESC) AS rnk
             FROM 
                employee) tmp
        WHERE rnk = N
  );
END

其他窗口函数

4. 查询部门工资最高的员工

-- 方法一：使用JOIN和IN语句
SELECT D.Name AS Department, E.Name AS Employee, E.Salary
FROM Department D
INNER JOIN Employee E
ON D.DepartmentId = E.Id
WHERE (
	E.DepartmentId, E.Salary) IN (
    							SELECT DepartmentId, MAX(Salary)
    							FROM Employee
    							GROUP BY DepartmentId);

-- 方法二：使用窗口函数
SELECT D.Name AS Department, E.Name AS Employee, E.Salary
FROM (SELECT *,
		DENSE_RANK() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) rnk
	  FROM Employee) E
INNER JOIN Department D
ON D.DepartmentId = E.Id
WHERE rnk = 1;

5. 查询部门工资前三高的所有员工

-- 方法一：使用窗口函数
SELECT D.Name AS Department, E.Name AS Employee, E.Salary
FROM
    (SELECT *, 
    DENSE_RANK() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) rnk
    FROM Employee) E
INNER JOIN Department D 
ON E.DepartmentId = D.Id 
AND rnk < 4;

-- 方法二：使用JOIN和子查询
SELECT
    D.Name AS Department, E1.Name AS Employee, E1.Salary
FROM
    Employee E1
INNER JOIN Department D 
ON E1.DepartmentId = D.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT E2.Salary)
        FROM
            Employee E
        WHERE
            E2.Salary > E1.Salary AND E1.DepartmentId = E2.DepartmentId
        );

6. 查询连续出现的数字

-- 方法一：三表连接
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;
    
-- 方法二：使用窗口函数lead()
SELECT 
	DISTINCT num AS ConsecutiveNums
FROM
	(SELECT num,
    lead(num,1) OVER() AS num1,
    lead(num,2) OVER() AS num2
    FROM logs) AS c
WHERE c.num = c.num1 AND c.num1 = c.num2

-- 方法三：（重点理解）
SELECT 
	DISTINCT(num) AS ConsecutiveNums
FROM (
	SELECT num, (row_number() OVER(ORDER BY id )-row_number() over(PARTITION BY num 		   ORDER BY id)) rnk
	FROM Logs) tmp
GROUP BY rnk,num
HAVING COUNT(rnk) >= 3;

7. 重新格式化部门表（行转列问题）

SELECT id, 
    SUM(CASE WHEN month = "Jan" Then revenue ELSE null END) AS Jan_Revenue,
    SUM(CASE WHEN month = "Feb" Then revenue ELSE null END) AS Feb_Revenue,
    SUM(CASE WHEN month = "Mar" Then revenue ELSE null END) AS Mar_Revenue,
    SUM(CASE WHEN month = "Apr" Then revenue ELSE null END) AS Apr_Revenue,
    SUM(CASE WHEN month = "May" Then revenue ELSE null END) AS May_Revenue,
    SUM(CASE WHEN month = "Jun" Then revenue ELSE null END) AS Jun_Revenue,
    SUM(CASE WHEN month = "Jul" Then revenue ELSE null END) AS Jul_Revenue,
    SUM(CASE WHEN month = "Aug" Then revenue ELSE null END) AS Aug_Revenue,
    SUM(CASE WHEN month = "Sep" Then revenue ELSE null END) AS Sep_Revenue,
    SUM(CASE WHEN month = "Oct" Then revenue ELSE null END) AS Oct_Revenue,
    SUM(CASE WHEN month = "Nov" Then revenue ELSE null END) AS Nov_Revenue,
    SUM(CASE WHEN month = "Dec" Then revenue ELSE null END) AS Dec_Revenue
FROM Department
GROUP BY id;

-- 当一个单元格中有多个数据时，CASE WHEN只会提取当中的第一个数据
-- 使用聚合函数可以解决一个单元格内含有多个数据的情况（聚合函数就是用来输入多个数据，输出一个数据的）

8. 换座位问题

SELECT
    (CASE
        WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
        WHEN MOD(id, 2) != 0 AND counts = id THEN id
        ELSE id - 1
    END) AS id,
    student
FROM
    seat,
    (SELECT COUNT(*) AS counts FROM seat) AS seat_counts
ORDER BY id ASC;

-- 注意CASE WHEN的写法

9. 体育馆人流量

SELECT DISTINCT a.*
FROM stadium a,stadium b,stadium c
WHERE ((a.id = b.id-1 AND b.id+1 = c.id) OR
       (a.id-1 = b.id AND a.id+1 = c.id) OR
       (a.id-1 = c.id AND c.id-1 = b.id))
  AND (a.people>=100 AND b.people>=100 AND c.people>=100)
ORDER BY a.id;

-- 不要忘记去重，因为比如 5,6,7,8，6 既是 5,6,7 的中间值也是 6,7,8 的最小值

10. 行程与用户

SELECT T.request_at AS Day, 
   	ROUND(
   			SUM(
   				IF(T.STATUS = 'completed',0,1)
   			)
   			/ 
   			COUNT(T.STATUS),
   			2
   	) AS 'Cancellation Rate'
   FROM Trips AS T
   INNER JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned ='No')
   INNER JOIN Users AS U2 ON (T.driver_id = U2.users_id AND U2.banned ='No')
   WHERE T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
   GROUP BY T.request_at;
   
   -- ROUND部分也可以直接用ROUND(AVG(Status <> 'completed'), 2)