力扣sql中等篇练习(十四)

力扣sql中等篇练习(十四)

1 最后一个能进入电梯的人

1.1 题目内容

1.1.1 基本题目信息

1.1.2 示例输入输出

1.2 示例sql语句

# 在表某一个范围内的可以考虑自连接的方式,注意连接的表只需要精准的字段
# 需要排序是因为它需要找到最后一个上车的用户
SELECT q1.person_name
FROM
(
    SELECT person_name,turn
    FROM Queue
)q1
INNER JOIN 
(
   SELECT turn,weight
   FROM Queue
)q2
ON q1.turn>=q2.turn
GROUP BY q1.turn
HAVING sum(q2.weight)<=1000
ORDER BY q1.turn desc
limit 1 

1.3 运行截图

2 每月交易Ⅱ

2.1 题目内容

2.1.1 基本题目信息

2.1.2 示例输入输出

a 示例输入

b 示例输出

2.2 示例sql语句

# 需要新开一个列 区分退单还是批准
# 无论是批准还是拒绝,都可以退单
SELECT t1.month,t1.country,
       SUM(IF(t1.state='approved' AND t1.flag=1,1,0)) approved_count,
       SUM(IF(t1.state='approved' AND t1.flag=1,amount,0)) approved_amount,
       SUM(IF(t1.flag=0,1,0)) chargeback_count,
       SUM(IF(t1.flag=0,amount,0)) chargeback_amount
FROM
(
    -- 0代表退单,1代表批准
    SELECT t.id,t.country,t.state,t.amount,date_format(c.trans_date,'%Y-%m') month,0 flag
    FROM Transactions t 
    RIGHT JOIN Chargebacks c
    ON t.id=c.trans_id
    UNION ALL
    SELECT id,country,state,amount,date_format(trans_date,'%Y-%m') month,1 flag
    FROM Transactions
    WHERE state='approved'
)t1
GROUP BY t1.month,t1.country

2.3 运行截图

3 查询球队积分

3.1 题目内容

3.1.1 基本题目信息1

3.1.2 基本题目信息2

3.1.3 示例输入输出

3.2 示例sql语句

# 有的球队可能只打过主场或者只打过客场
# UNION ALL两表连接时就算有group by 也可以在其后写union all
SELECT t1.team_id,t1.team_name,IFNULL(t3.num_points,0) num_points
FROM Teams t1
LEFT JOIN
(
    SELECT t2.host_team,sum(t2.score) num_points
    FROM
    (
        SELECT host_team,sum(IF(host_goals>guest_goals,3,IF(host_goals=guest_goals,1,0))) score
        FROM Matches
        GROUP BY host_team   
        UNION ALL
        SELECT guest_team host_team,sum(IF(host_goals<guest_goals,3,IF(host_goals=guest_goals,1,0))) score
        FROM Matches
        GROUP BY guest_team   
    )t2
    GROUP BY t2.host_team
)t3
ON t1.team_id=t3.host_team
ORDER BY num_points desc,t1.team_id asc

3.3 运行截图