175. 组合两个表

# Write your MySQL query statement below
select FirstName, LastName, City, State
from Person
         left join Address on Person.PersonId = Address.PersonId

176. 第二高的薪水

解1：

# Write your MySQL query statement below
select ifnull(
               (select Salary
                from (select @n := @n + 1 as ranking, Salary
                      from (select @n := 0) as a,
                           (select distinct Salary from Employee) as b
                      order by Salary desc) as c
                where c.ranking = 2), null) as SecondHighestSalary

解2：

1 2	# Write your MySQL query statement below select ifnull((select distinct Salary from Employee order by Salary desc limit 1,1),null) as SecondHighestSalary

错误解法：

# Write your MySQL query statement below
select IF(Salary is not null, Salary, null) as SecondHighestSalary
from (select @n := @n + 1 as ranking, Id, Salary
      from (select @n := 0) a,
           Employee b
      order by Salary desc) as c
where c.ranking = 2;
#返回的是{"headers": ["SecondHighestSalary"], "values": []}
#而预期是{"headers": ["SecondHighestSalary"], "values": [[null]]}

知识点

为查询结果增加一个自增列

#两句
set @rownum=0;
select (@rownum:=@rownum+1),colname from [tablename or (subquery) a];

#合成一句
select @rownum:=@rownum+1,colnum from (select @rownum:=0) a,[tablename or (subquery) b];

注：在自增列取别名时注意rank是关键字！as rank会报错！

`IF`和`IFNULL`作为表达式用

1	IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 != 0 and expr1 != NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

1	IFNULL(expr1,expr2)

假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

参考：SQL的IF语句

`limit`与`limit+offset`选出排第n位的值

limit接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。

SQL查询语句中的 limit 与 offset 的区别：

limit y 分句表示: 读取 y 条数据
limit x, y 分句表示: 跳过 x 条数据，从第 x+1 条开始读取 y 条数据
limit y offset x 分句表示: 跳过 x 条数据，从第 x+1 条开始读取 y 条数据

所以查询排名第n位的值应该用：

1
2
3

LIMIT 1 OFFSET n-1
或
LIMIT n-1,1

参考：SQL查询语句中的 limit 与 offset 的区别

返回空表与返回`NULL`值（错解原因）

返回0条记录（空表，啥也没返回）和返回null（此时null是个值，是个结果返回）的区别：查询一个空表返回空（啥也没有），查询一个全是null无其他值的表返回null。

用子查询：子查询可以在任意地方使用，相当于查询的列。子查询数据作为虚表嵌套来查询虚表，查询不到返回null，因此解2可以优化为：

1 2	# Write your MySQL query statement below select (select distinct Salary from Employee order by Salary desc limit 1,1) as SecondHighestSalary

验证：

1	select NULL;

返回的是NULL值。

177. 第N高的薪水

解1：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    RETURN (
        # Write your MySQL query statement below.
        select ifnull(
                       (select Salary
                        from (select @n := @n + 1 as ranking, Salary
                              from (select @n := 0) as a,
                                   (select distinct Salary from Employee) as b
                              order by Salary desc) as c
                        where c.ranking = N), null) as getNthHighestSalary
    );
END

解2（但是如果只能在return里作答的话，set那句加在了非答题区域）：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N = N - 1;
    RETURN (
        # Write your MySQL query statement below.
        select ifnull((select distinct Salary from Employee order by Salary desc limit N,1),
                      null) as getNthHighestSalary
    );
END

知识点

`limit`子句不能跟计算式

参考题解：排名第N高意味着要跳过N-1个薪水，由于无法直接用limit N-1，所以需先在函数开头处理N为N=N-1。
注：这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数（意味着0、负数、小数都不行）或者单一变量（意味着不能用表达式），也就是说想取一条，limit 2-1、limit 1.1这类的写法都是报错的。

178. 分数排名

解1：

1 2	# Write your MySQL query statement below select Score,dense_rank() over(order by Score desc) as `Rank` from Scores

解2（没有窗口函数时）：

# Write your MySQL query statement below
select Score,
       (select count(distinct Score) from Scores as a where a.Score >= b.Score) as `Rank`
from Scores as b
order by Score desc

知识点

MySQL 8.0的排名函数

参考上一题题解里的第六点：窗口函数

参考博文：MySQL rank() over、dense_rank() over、row_number() over 用法介绍

rank()：条件相同时排名相同，排名间断不连续，有跳级。
dense_rank()：条件相同时排名相同，排名连续不间断，无跳级。
row_number()：条件相同时排名不相同，排名连续不间断，相当于行号。

必须要要与其搭档over()配套使用，over()中的参数常见的有两个，分别是：

partition by：按某字段切分。
order by：与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据。

当MySQL没有8.0时怎么办

给一个分数X算出它的排名Rank——思路：先提取出大于等于X的所有分数集合H，将H去重后的元素个数就是X的排名。比如你考了99分，但最高的就只有99分，那么去重之后集合H里就只有99一个元素，个数为1，因此你的Rank为1。

参考题解。

180. 连续出现的数字

解1：

# Write your MySQL query statement below
select distinct a.Num as ConsecutiveNums
from `Logs` a,
     `Logs` b,
     `Logs` c
where (a.Id + 1 = b.Id and a.Num = b.Num)
  and (a.Id + 2 = c.Id and a.Num = c.Num)

解2（利用lag和lead函数）：

# Write your MySQL query statement below
select distinct Num as ConsecutiveNums
from (select Num, lag(Num, 1) over () as front, lead(Num, 1) over () as behind from `Logs`) as c
where Num = front
  and Num = behind

评论里的一种利用case-when语句的方法：

select distinct Num as ConsecutiveNums
from (
         select Num,
                case
                    when @prev = Num then @count := @count + 1 #如果上一行已经给prev赋值了，这一行还是同一个Num的话，就会触发这一句when，然后count+1计数
                    when (@prev := Num) is not null then @count := 1 #给prev变量赋新值并且直接进入count+1计数，因为is not null默认为true
                    end as CNT
         from Logs,
              (select @prev := null, @count := null) as t
     ) as temp
where temp.CNT >= 3 #3换成任何值可以满足不同题目给出的连续N次出现的数字

注：如果有三行连续的Num为1的话，中间select出来的(Num,CNT)结果应该是(1,1),(1,2),(1,3)，然后where语句就会选出第三行符合条件。

另一种采用两个表之间的关系比较的更牛的方法参考：题解。

知识点

MySQL 8.0：lag()和lead()

The LAG() function is used to get value from row that precedes the current row.

The LEAD() function is used to get value from row that succedes the current row.

LEAD(expr, N, default) 
          OVER (Window_specification | Window_name)

LAG(expr, N, default) 
          OVER (Window_specification | Window_name)

注：lag和lead后面必须跟over()，如果over()里参数为空则默认在所有行里操作。

参考：MySQL | LEAD() and LAG() Function

181. 超过经理收入的员工

# Write your MySQL query statement below
select a.Name as `Employee`
from Employee as a
         left join Employee as b
                   on a.ManagerId = b.Id
where a.ManagerId is not null
  and a.Salary > b.Salary

注：where里一开始我还很仔细地加上了b.ManagerId is null的条件，结果用例里有一个套娃式的多层经理关系把我看傻了："rows": {"Employee": [[1, "Mark", 40000, 3], [3, "Jack", 30000, 2], [2, "Alan", 20000, null]]}，好家伙。

182. 查找重复的电子邮箱

# Write your MySQL query statement below
select Email
from (SELECT Email, count(*) as num FROM Person group by Email) as c
where c.num > 1

更好的解法，用having：

1 2	# Write your MySQL query statement below select Email FROM Person GROUP BY Email HAVING count(*)>1

参考：SQL HAVING 子句

183. 从不订购的客户

# Write your MySQL query statement below
select `Name` as `Customers`
from Customers as c
         left join Orders as o
                   on c.Id = o.CustomerId
where o.Id is null

关于SQL里的join连接

184. 部门工资最高的员工

# Write your MySQL query statement below
select d.`Name` as `Department`, b.`Name` as `Employee`, b.Salary
from (select a.`Name`, a.Salary, a.DepartmentId
      from Employee a
               right join
           (select DepartmentId, max(Salary) as max_s from Employee group by DepartmentId) c
           on a.Salary = c.max_s and a.DepartmentId = c.DepartmentId) b
         inner join Department as d
                    on b.DepartmentId = d.Id

P.S.官方题解给出了一种in同时用在两个字段上的骚操作。

185. 部门工资前三高的所有员工

# Write your MySQL query statement below
select Department, Employee, Salary
from (select d.`Name`                                                           as `Department`,
             e.`Name`                                                           as Employee,
             e.Salary,
             dense_rank() over (partition by DepartmentId order by Salary desc) as ranking
      from Employee e
               inner join Department d
                          on e.DepartmentId = d.Id) a
where a.ranking <= 3

题解里另一种不用窗口函数的解法。

知识点

`over()`函数里两个参数的使用语法

1	over（partition by columnname1 order by columnname2）

参考SQL over的作用及用法。

196. 删除重复的电子邮箱

# Write your MySQL query statement below
delete
from Person
where Id not in (select Id from (select min(Id) as `Id` from Person group by Email) c)

P.S.官方题解采用多表联结删除的方法，有详细解释

知识点

You can’t specify target table ‘表名’ for update in FROM clause错误

1 2	# Write your MySQL query statement below delete from Person where Id not in (select min(Id) as `Id` from Person group by Email)

这个提交报如题错误，原因是在MySQL里不能先select一个表的记录，再按此条件进行更新和删除同一个表的记录。这时需要再通过中间表select一遍，规避错误。

参考博客

197. 上升的温度

# Write your MySQL query statement below
select w1.Id
from Weather w1,
     Weather w2
where DATE_SUB(w1.recordDate, INTERVAL 1 DAY) = w2.recordDate
  and w1.temperature > w2.temperature

知识点

SQL DATE函数

262. 行程和用户

# Write your MySQL query statement below
select Request_at                                                              as `Day`,
       cast(count(Status != 'completed' or null) / count(*) as decimal(10, 2)) as `Cancellation Rate`
from Trips as t,
     (select * from Users where Role = 'client') as cli,
     (select * from Users where Role = 'driver') as dri
where t.Client_Id = cli.Users_Id
  and t.Driver_Id = dri.Users_Id
  and cli.Banned = 'No'
  and
  #dri.Banned='No' and
    t.Request_at between '2013-10-01' and '2013-10-03'
group by Request_at

注：倒数第三行注释掉是因为中英文版力扣题意不一样，英文定义的非禁止用户原话是“unbanned users (both client and driver must not be banned)”，中文版翻译直接把括号部分忽略掉。忽略就算了吧我以为出题人只是语文不及格，结果有一个测试用例就是愉悦地在Users表里设置了3个Banned的司机，计算出来的非禁止用户生成订单也把这些司机的订单算进去了，Banned字段有效的仅仅是乘客，原来出题人思想道德也不及格，这道题“困难”水平是难在了这里，我直接给你妈拍手叫好。

知识点

保留两位小数

1 2	round(number,2) #四舍五入 cast(number as decimal(10,2)) #仅保留前两位，去尾法

参考：Sql 保留两位小数

`count()`里加上条件表达式一定要加`or NULL`

count(‘任意内容’)都会统计出所有记录数，因为count只有在遇见null时不计数，即count(null)==0，因此前者单引号内不管输入什么值都会统计出所有记录数，所以如果要在count()函数里加上表达式的话一定要在后面加上or NULL。

参考：sql 语句中count()有条件的时候为什么要加上or null

当需要`count()`的东西是二值化的时候改用`avg()`

在题解里看到一个拍案叫绝的方法，原理：求多个0和1的平均，最后得到的就是1的比例

595. 大的国家

1 2	# Write your MySQL query statement below SELECT name,population,area FROM World WHERE area>3000000 OR population>25000000

596. 超过5名学生的课

1 2	# Write your MySQL query statement below select class from courses group by class having count(distinct student)>=5

# Write your MySQL query statement below
select class
from (select class, count(distinct student) as num from courses group by class) c
where c.num >= 5

601. 体育馆的人流量

# Write your MySQL query statement below
select f.Id as id, f.visit_date, f.people
from (select *
      from (select *, lead(b.CNT, 1) over () as after
            from (select *,
                         case
                             when s.people >= 100 then @count := @count + 1
                             when s.id is not null then @count := 0
                             end as CNT
                  from Stadium as s,
                       (select @count := 0) as c) as b) as d
      where (d.after = 0 or d.after is null)
        and d.CNT >= 3) as e,
     Stadium as f
where f.Id between e.Id - e.CNT + 1 and e.Id

评论区提到的利用窗口函数的另一种没这么笨重的方法（用了一个临时表）：

# Write your MySQL query statement below
select distinct t2.*
from (
         select *,
                lead(people, 1) over (order by visit_date ) as p2,
                lead(people, 2) over (order by visit_date ) as p3
         from Stadium
     ) t,
     Stadium t2
where t.people >= 100
  and p2 >= 100
  and p3 >= 100
  and t2.id >= t.id
  and t2.id - 2 <= t.id

620. 有趣的电影

# Write your MySQL query statement below
select id, movie, description, rating
from cinema
where description != 'boring'
  and id % 2 != 0
order by rating desc

就这么简单一道题还有人玩得骚，用位运算，奇数和1做&操作得到1，偶数和1做&操作得到0。：

SELECT
	id,
	movie,
	description,
	rating
FROM
	cinema
WHERE
	id & 1
AND description <> 'boring'
ORDER BY
	rating DESC

626. 换座位

# Write your MySQL query statement below
select id,
       ifnull(case
                  when id & 1 then lead(student, 1) over ()
                  when id is not null then lag(student, 1) over ()
                  end, student) as `student`
from seat

看了一圈答案发现大家都是换id然后最后再按id排一下序的（用if交换id题解），原来小丑是我自己。

627. 变更性别

# Write your MySQL query statement below
update salary
set sex=
        case
            when sex = 'm' then 'f'
            when sex = 'f' then 'm'
            end

评论区有用if来判断的，更简洁：

1	update salary set sex = if(sex = 'm','f','m')

1179. 重新格式化部门表

# Write your MySQL query statement below
select id,
       SUM(Jan_Revenue) as 'Jan_Revenue',
       SUM(Feb_Revenue) as 'Feb_Revenue',
       SUM(Mar_Revenue) as 'Mar_Revenue',
       SUM(Apr_Revenue) as 'Apr_Revenue',
       SUM(May_Revenue) as 'May_Revenue',
       SUM(Jun_Revenue) as 'Jun_Revenue',
       SUM(Jul_Revenue) as 'Jul_Revenue',
       SUM(Aug_Revenue) as 'Aug_Revenue',
       SUM(Sep_Revenue) as 'Sep_Revenue',
       SUM(Oct_Revenue) as 'Oct_Revenue',
       SUM(Nov_Revenue) as 'Nov_Revenue',
       SUM(Dec_Revenue) as 'Dec_Revenue'
from (select id,
             case when month = 'Jan' then revenue end as 'Jan_Revenue',
             case when month = 'Feb' then revenue end as 'Feb_Revenue',
             case when month = 'Mar' then revenue end as 'Mar_Revenue',
             case when month = 'Apr' then revenue end as 'Apr_Revenue',
             case when month = 'May' then revenue end as 'May_Revenue',
             case when month = 'Jun' then revenue end as 'Jun_Revenue',
             case when month = 'Jul' then revenue end as 'Jul_Revenue',
             case when month = 'Aug' then revenue end as 'Aug_Revenue',
             case when month = 'Sep' then revenue end as 'Sep_Revenue',
             case when month = 'Oct' then revenue end as 'Oct_Revenue',
             case when month = 'Nov' then revenue end as 'Nov_Revenue',
             case when month = 'Dec' then revenue end as 'Dec_Revenue'
      from Department) c
group by id

参考思路：SQL 语句题 | 重新格式化部门表，可以SUM()和case合用精简语句。

知识点

为查询结果增加一个自增列

IF和IFNULL作为表达式用

limit与limit+offset选出排第n位的值

返回空表与返回NULL值（错解原因）

知识点

limit子句不能跟计算式

知识点