LeetCode sql语句 学习专题【Medium】

题目链接如下： https://leetcode.com/problemset/database/

---

目录

626. Exchange Seats

178. Rank Scores

180. Consecutive Numbers

184. Department Highest Salary

177. Nth Highest Salary

---

626. Exchange Seats

交换相邻两个记录的某个字段，使用CASE WHEN，使用select count(*) from 统计有多少字段

# Write your MySQL query statement below
select (
    CASE
        WHEN MOD(id,2)=1 and id!=counts THEN id+1
        WHEN MOD(id,2)=0 THEN id-1
        ELSE id
    END
)as id,student from
(
    select count(*) as counts from seat    
)A,seat
order by id ASC;

---

178. Rank Scores

统计分数排名，先选出不同的分数，根据大小关系确定排名

# Write your MySQL query statement below
select Score,(
    select count(*) from ( select distinct Score as s from Scores)t where s>=Score
)Rank 
from Scores order by Score DESC

 

---

180. Consecutive Numbers

问记录里连续出现至少3次的数字是什么。左连接两次，并注意distinct

# Write your MySQL query statement below

select distinct res.aNum as  ConsecutiveNums from (
select tmp.Id,aNum,bNum,c.Num as cNum from
(
    select a.Id,a.Num as aNum,b.Num as bNum from Logs as a 
    left join Logs b on a.Id+1 = b.Id
    )tmp left join Logs c on c.Id = tmp.Id+2
)res 
where res.aNum = res.bNum and res.bNum = res.cNum

#标准解法
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
;

---

 

184. Department Highest Salary

找到各个部门里的最高工资，先选出各个部门的最高工资，判断对应DepartmentId和Salary是都存在其中

# Write your MySQL query statement below
select tmp.DepartmentName as Department ,tmp.name as Employee,Salary from (
    select a.Id,a.Name,a.Salary,a.DepartmentId,b.Name as DepartmentName from Employee a  join Department b on a.DepartmentId = b.Id
)tmp 
where (tmp.DepartmentId , Salary) in
(
    select DepartmentId,max(salary) from Employee group by DepartmentId
)

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

不使用 left join的原因是，左连接出null 字段，左边没有的内容会出现null

---

177. Nth Highest Salary

找到第N大的工资，需要用到函数

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