leetcode刷题 - SQL - 中等、困难

1.  176. 第二高的薪水

筛选出第二大

查询并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null(Pandas 则返回 None) 。查询结果如下例所示。

666中等的第一题就上强度

强行解法

select max(salary) as SecondHighestSalary from Employee 
where salary!=(select max(salary) as salary from Employee);

万一是其他次序的解法，使用limit（看2.）

1.标量子查询，查询为空的时候会填充null

2.limit n,m 等价于 limit m offset n ，表示跳过开头的n行，返回接下来的m条数据。降序之后的第2条数据就是limit 1,1

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

第三题提供的思路，但是无法实现重复的最大值

-- 每个部门第一第三高
SELECT S.NAME, S.EMPLOYEE, S.SALARY
  FROM (SELECT D.NAME,
               T.NAME EMPLOYEE,
               T.SALARY,
               ROW_NUMBER() OVER(PARTITION BY T.DEPARTMENTID ORDER BY T.SALARY DESC) RN
          FROM EMPLOYEE T
          LEFT JOIN DEPARTMENT D
            ON T.DEPARTMENTID = D.ID) S
 WHERE S.RN = 1 OR S.RN = 3

2. 177. 第N高的薪水

写函数

查询 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询结果应该为 null 。

MySQL高级-自定义函数-csdn

有点玄学的函数

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set n=n-1;
  RETURN (
       SELECT distinct salary FROM Employee ORDER BY salary DESC LIMIT N, 1
  );
END

 3. 178. 分数排名

考察同3.

select score, 
       dense_rank() over(order by score desc) as 'rank' from Scores

4. 180. 连续出现的数字

连续值

SELECT DISTINCT Num AS ConsecutiveNums FROM Logs 
WHERE (Id+1, Num) IN (SELECT * FROM Logs)
AND (Id+2, Num) IN (SELECT * FROM Logs)

5. 184. 部门工资最高的员工

多个最大值, 开窗

select d.name as Department, e.name as Employee, e.salary as Salary
from Employee e left join Department d on e.departmentId = d.id
where (e.departmentId, e.salary) in (select distinct departmentId, max(salary) from Employee 
group by departmentId)

开窗函数实现

select Department, Employee, Salary from 
(select d.name Department, 
        e.name Employee, 
        e.salary Salary, 
        rank() over(partition by e.departmentId order by e.salary DESC) rankA 
from Employee e left join Department d on e.departmentId = d.id) ranktbl 
where rankA = 1

rank() 

SQL开窗函数（窗口函数）详解-CSDN博客

over()  一类是聚合开窗函数，一类是排序开窗函数。  

调用格式为：函数名(列名) OVER(partition by 列名 order by列名) 。

聚合函数对一组值执行计算并返回单一的值，如sum()，count()，max()，min()， avg()等

常与group by子句连用。除了 COUNT 以外，聚合函数忽略空值

想知道各个地区的前几名、各个班的前几名。这时候需要每一组返回多个值

SQL 标准允许将所有聚合函数用作开窗函数，用OVER 关键字区分开窗函数和聚合函数

开窗函数与聚合函数一样，也是对行集组进行聚合计算

row_number () over()

ROW_NUMBER() OVER(PARTITION BY T.DEPARTMENTID ORDER BY T.SALARY DESC) RN

但是这个无法取出重复的最大值

对相等的值不进行区分，其实就是行号，相等的值对应的排名不同，序号从1到n连续。

rank() over()：

相等的值排名相同，但若有相等的值，则序号从1到n不连续。如果有两个人都排在第3名，则没有第4名。1233567

dense_rank() over()：

对相等的值排名相同，但序号从1到n连续。如果有两个人都排在第一名，1123456

ntile( n ) over()：

可以看作是把有序的数据集合平均分配到指定的数量n的桶中,将桶号分配给每一行，排序对应的数字为桶号，序号从1到n连续。如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的数据条数最多相差1。1111222333444555（放三轮，1桶多一个）

6. 550. 游戏玩法分析 IV

日期计算，查询相互运算

示例 1：

输入：
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
输出：
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
解释：
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33

select round(
    (select count(player_id) from Activity  -- 算出所有条件玩家数
        where (player_id,event_date-INTERVAL 1 day) in (select player_id,min(event_date) from Activity group by player_id))
    /
    (select count(distinct player_id) from Activity)  -- 算出所有玩家数
    ,2) as fraction 

7. 570. 至少有5名直接下属的经理

混进来一个简单题

# Write your MySQL query statement below
select e1.name from Employee e1 left join Employee e2 on e1.id = e2.managerId
group by e1.id 
having count(*)>=5

8. 602. 好友申请 II ：谁有最多的好友

with用法

# Write your MySQL query statement below
with t1 as (
select requester_id as id1,
       accepter_id as id2 
from RequestAccepted 
union all 
select accepter_id as id1,
       requester_id as id2 
from RequestAccepted
)

select id1 as id,
       count(distinct id2) as num 
from t1 
group by 1
order by 2 desc 
limit 1

9. 608. 树节点

case

select
    id,
    case when p_id is null then "Root"
         when id not in (select ifnull(p_id,'s') from Tree) then "Leaf"
         else "Inner"
    end as type
from
    Tree

10. 585. 2016年的投资

编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：
他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。
tiv_2016 四舍五入的 两位小数 。

查询结果格式如下例所示。

输入：
Insurance 表：
+-----+----------+----------+-----+-----+
| pid | tiv_2015 | tiv_2016 | lat | lon |
+-----+----------+----------+-----+-----+
| 1   | 10       | 5        | 10  | 10  |
| 2   | 20       | 20       | 20  | 20  |
| 3   | 10       | 30       | 20  | 20  |
| 4   | 10       | 40       | 40  | 40  |
+-----+----------+----------+-----+-----+
输出：
+----------+
| tiv_2016 |
+----------+
| 45.00    |
+----------+
解释：
表中的第一条记录和最后一条记录都满足两个条件。
tiv_2015 值为 10 与第三条和第四条记录相同，且其位置是唯一的。

第二条记录不符合任何一个条件。其 tiv_2015 与其他投保人不同，并且位置与第三条记录相同，这也导致了第三条记录不符合题目要求。
因此，结果是第一条记录和最后一条记录的 tiv_2016 之和，即 45 。

SELECT ROUND(SUM(tiv_2016),2) tiv_2016
FROM 
(
SELECT *,
COUNT(*) OVER(PARTITION BY tiv_2015 ) cnt_tiv_2015,
COUNT(*) OVER(PARTITION BY lat,lon ) cnt_lat
FROM Insurance 
)t
WHERE cnt_tiv_2015>1 AND cnt_lat=1

学会开窗函数！

这里是一张表

-———————————————————————————————————————-
| pid | tiv_2015 | tiv_2016 | lat | lon |
| --- | -------- | -------- | --- | --- |
| 1   | 10       | 5        | 10  | 10  |
| 2   | 20       | 20       | 20  | 20  |
| 3   | 10       | 30       | 20  | 20  |
| 4   | 10       | 40       | 40  | 40  |
-———————————————————————————————————————-

如果我这么写

select 
    *,
    count(*) over(PARTITION by tiv_2015)  tiv_5,
    count(*) over(PARTITION by lat,lon) l
from Insurance
WHERE tiv_5>1 and l=1

Unknown column 'tiv_5' in 'where clause'，这时候筛选，表里没有这两列

去掉WHERE筛选，就表示取出所有的数据之后再加上两列

select 
    *,
    count(*) over(PARTITION by tiv_2015)  tiv_5,
    count(*) over(PARTITION by lat,lon) l
from Insurance

| pid | tiv_2015 | tiv_2016 | lat | lon | tiv_5 | l |
| --- | -------- | -------- | --- | --- | ----- | - |
| 1   | 10       | 5        | 10  | 10  | 3     | 1 |
| 3   | 10       | 30       | 20  | 20  | 3     | 2 |
| 2   | 20       | 20       | 20  | 20  | 1     | 2 |
| 4   | 10       | 40       | 40  | 40  | 3     | 1 |

666！开窗函数的结果给到了每一个对应的分组中！

11. 626. 换座位

典，仔细看，奇偶对调

示例 1:

输入: 
Seat 表:
+----+---------+
| id | student |
+----+---------+
| 1  | Abbot   |
| 2  | Doris   |
| 3  | Emerson |
| 4  | Green   |
| 5  | Jeames  |
+----+---------+
输出: 
+----+---------+
| id | student |
+----+---------+
| 1  | Doris   |
| 2  | Abbot   |
| 3  | Green   |
| 4  | Emerson |
| 5  | Jeames  |
+----+---------+
解释:
请注意，如果学生人数为奇数，则不需要更换最后一名学生的座位。

select s1.id,s2.student from Seat as s1 left join Seat as s2 on s1.id-1=s2.id
where s1.id%2=0
union 
select s1.id,s2.student from Seat as s1 left join Seat as s2 on s1.id=s2.id-1
where s1.id%2=1 and s2.student is not null
union
SELECT *
FROM Seat
WHERE id = (
    SELECT MAX(id)
    FROM Seat
    HAVING MAX(id) % 2 = 1
)
order by id

评论区带佬思路

SELECT (CASE 
            WHEN (id%2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
            WHEN (id%2) = 1 THEN id+1
            ElSE id-1
        END) AS id, student
FROM seat
ORDER BY id;

12. 1045. 买下所有产品的客户

distinct和count

秒了  有陷阱

select customer_id from Customer 
group by customer_id
having count(distinct product_key)=(select count(distinct product_key) from Product)

13. 1070. 产品销售分析 III

垃圾题，业务表述不清晰，折磨人有一套，🐕都不看

select product_id, year first_year, quantity, price
from sales
where (product_id, year) in
(select product_id, min(year)
from sales 
group by product_id)

14. 1158. 市场分析 I

easy

select u.user_id buyer_id,
       u.join_date join_date,
       sum(if(year(o.order_date)=2019,1,0)) orders_in_2019 
from Users u left join Orders o on u.user_id=o.buyer_id
group by u.user_id 

15. 1164. 指定日期的产品价格

死活写不出来，回头再战100次

输入：
Products 表:
+------------+-----------+-------------+
| product_id | new_price | change_date |
+------------+-----------+-------------+
| 1          | 20        | 2019-08-14  |
| 2          | 50        | 2019-08-14  |
| 1          | 30        | 2019-08-15  |
| 1          | 35        | 2019-08-16  |
| 2          | 65        | 2019-08-17  |
| 3          | 20        | 2019-08-18  |
+------------+-----------+-------------+
输出：
+------------+-------+
| product_id | price |
+------------+-------+
| 2          | 50    |
| 1          | 35    |
| 3          | 10    |
+------------+-------+

编写一个解决方案，找出在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。

select t.product_id, t.new_price as price
from (select *, row_number() over (PARTITION BY product_id order by change_date desc) as row_num
from Products
where change_date<='2019-08-16') as t
where t.row_num=1

union

select product_id, 10 as price 
from Products 
group by product_id
having min(change_date)>'2019-08-16'

16. 1174. 即时食物配送 II

如果顾客期望配送日期和下单日期相同，则该订单称为 「即时订单」，否则称为「计划订单」。

「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。

编写解决方案以获取即时订单在所有用户的首次订单中的比例。保留两位小数。

结果示例如下所示：

输入：
Delivery 表：
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 2           | 2019-08-02 | 2019-08-02                  |
| 3           | 1           | 2019-08-11 | 2019-08-12                  |
| 4           | 3           | 2019-08-24 | 2019-08-24                  |
| 5           | 3           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
| 7           | 4           | 2019-08-09 | 2019-08-09                  |
+-------------+-------------+------------+-----------------------------+
输出：
+----------------------+
| immediate_percentage |
+----------------------+
| 50.00                |
+----------------------+
解释：
1 号顾客的 1 号订单是首次订单，并且是计划订单。
2 号顾客的 2 号订单是首次订单，并且是即时订单。
3 号顾客的 5 号订单是首次订单，并且是计划订单。
4 号顾客的 7 号订单是首次订单，并且是即时订单。
因此，一半顾客的首次订单是即时的。

-- 先把所有的首次订单薅出来，与计划订单无关
with tem as 
  (select customer_id,order_date,customer_pref_delivery_date from Delivery
  where (customer_id,order_date) in 
    (select customer_id,min(order_date) from Delivery group by customer_id))

# 相除就行了
select 
  round(100*sum(if(order_date=customer_pref_delivery_date,1,0))/count(*),2) as immediate_percentage 
from tem

17. 1193. 每月交易 I

easy

select  concat(year(trans_date),'-',LPAD(month(trans_date),2,'0')) month,
        country,
        count(*) trans_count,
        sum(if(state='approved',1,0)) approved_count ,
        sum(amount) trans_total_amount,
        sum(if(state='approved',amount,0)) approved_total_amount
from Transactions 
group by country,year(trans_date),month(trans_date)

18. 1204. 最后一个能进入巴士的人

多个join的思路

有一队乘客在等着上巴士。然而，巴士有1000  千克 的重量限制，所以其中一部分乘客可能无法上巴士。

编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客，并报告 person_name 。题目测试用例确保顺位第一的人可以上巴士且不会超重

select person_name from Queue 
where turn = (select q1.turn
                from Queue q1 join Queue q2 on q1.turn >= q2.turn
                group by q1.turn
                having sum(q2.weight)<=1000
                order by q1.turn desc
                limit 1)

select q1.person_name
from Queue q1 join Queue q2 on q1.turn >= q2.turn
group by q1.turn
having sum(q2.weight)<=1000
order by q1.turn desc
limit 1

19. 1321. 餐馆营业额变化增长

又是窗口函数，没做出来，这个不难好好理解！！

计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。

结果按 visited_on 升序排序。

select 
    visited_on, amount, average_amount
from (
    select 
        distinct visited_on,  # 因为窗口函数是按照日期计算的。所以相同日期的结果也是相同的，直接去重即可
        sum(amount) over(order by visited_on range interval 6 day preceding) amount, # 按照日期排序，范围是当前日期和当前日期的前六天
        round(sum(amount) over(order by visited_on range interval 6 day preceding)/7, 2) average_amount # 同理
    from 
        Customer) t
where datediff(visited_on, (select min(visited_on) from Customer))>=6 #去除日期不足7日的结果
order by visited_on;

-- method 2
with t as
(
  select
      visited_on,
      sum(amount) amount
  from
      customer 
  group by
      visited_on
)
select
  a.visited_on,
  sum(b.amount) amount,
  round(sum(b.amount)/7,2) average_amount 
from
  t a,
  t b
where
  datediff(a.visited_on,b.visited_on) between 0 and 6
group by
  a.visited_on
having
  datediff(visited_on, (select min(visited_on) from customer)) >= 6
order by a.visited_on

datediff('2018-01-01','2018-01-05' )= -4

datediff('2018-01-05','2018-01-01') =  4

20. 1341. 电影评分

不难

题目太长，逻辑简单

# Write your MySQL query statement below
select name as results from (select m.user_id,user.name from MovieRating m join Users user on m.user_id=user.user_id
group by user_id
order by count(movie_id) desc,user.name
limit 1) t
union all
select title results from (select m.movie_id,sum(m.rating)/count(m.rating) s,movie.title from MovieRating m join Movies movie on m.movie_id=movie.movie_id
where year(m.created_at)='2020' and month(m.created_at)='2'
group by m.movie_id
order by s desc, movie.title
limit 1) t

 

21. 1393. 股票的资本损益

买卖，秒了

with t as 
(select stock_name, 
        operation,
        operation_day,
        if(operation='Buy',0-price,price) price 
from Stocks)

select stock_name,sum(price) capital_gain_loss from t
group by stock_name

22. 1907. 按分类统计薪水

秒

select 'Low Salary' category,count(*) accounts_count from Accounts
where income<20000
union
select 'Average Salary' category,count(*) accounts_count from Accounts
where income between 20000 and 50000
union
select 'High Salary' category,count(*) accounts_count from Accounts
where income>50000

23. 1934. 确认率

# Write your MySQL query statement below
with t as 
(select s.user_id,ifnull(action,'timeout') action 
from Signups s left join Confirmations c on s.user_id=c.user_id)

select user_id,ROUND(sum(if(action='confirmed',1,0))/count(*),2) confirmation_rate from t
group by user_id

            ==== 困难 ====

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

开窗！

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。（前三，可以并列）

编写解决方案，找出每个部门中 收入高的员工 。

# Write your MySQL query statement below
select dename Department,emname Employee,salary Salary from 
(select Department.name dename,
        Employee.name emname,
        salary,
        dense_rank() over(partition by departmentId order by salary desc) rk
from Employee join Department on Employee.departmentId=Department.id) t
where rk<4

25. 262. 行程和用户

多个with用法

题目太长，点进去自己看

with t1 as (
    select * from Trips where request_at between '2013-10-01' and '2013-10-03'
),
    t2 as (
    select * from Users where role='client' and banned='No'
),
    t3 as (
    select * from Users where role='driver' and banned='Yes'
    )
select request_at Day,(round(sum(if(SUBSTRING(status,1,3)='can',1,0))/count(*),2)) 'Cancellation Rate' from t2 left join t1 on t2.users_id=t1.client_id where status is not null and driver_id not in (select users_id from t3) 
group by request_at
order by Day

26. 连续

没看懂