LeetCode sql总结3

1098. 小众书籍

你需要写一段 SQL 命令，筛选出过去一年中订单总量 少于10本 的 书籍 。

注意：不考虑 上架（available from）距今 不满一个月 的书籍。并且 假设今天是 2019-06-23 。

p.s. left join和普通join 在on的后面跟条件的区别 left on+条件，只改变右边的值，无法改变左表的值，左表值仍然保留，没有where 的直接过滤作用 inner join 后的on + 条件，可以直接过滤条件 等同于where的作用

select book_id from orders
    where  orders.dispatch_date>='2018-06-23'
    group by book_id 
    having sum(quantity)>=10

select b.book_id, b.`name`
from books b left join orders o 
on b.book_id = o.book_id 
and o.dispatch_date >= '2018-06-23' 
-- 这里left join的连接条件有两个，若b中的某些记录不能同时满足，则仍会保留这些记录，只是o中的字段成为NULL
-- b中记录的o字段为NULL则表示该书籍在过去一年中订单量为0
where b.available_from <= '2019-05-23'
-- 但如果把available放到连接条件中，可能就会出现本该被筛掉的书籍由于left join被保留下来
group by b.book_id, b.`name` -- 这里b.name可有可无
having sum(o.quantity) < 10
or isnull(sum(o.quantity)); -- 如果o.quantity全是null则也符合要求

1132. 报告的记录 II

编写一段 SQL 来查找：在被报告为垃圾广告的帖子中，被移除的帖子的每日平均占比，四舍五入到小数点后 2 位。

select round(avg(l)*100,2) as  average_daily_percent
from(
    select count(distinct rid)/count(distinct tid) as l
    from (
        select tmp.post_id as tid ,action_date,r.post_id as rid
        from(
            select post_id,action_date from 
            actions
            where extra='spam'  
        ) tmp  left join Removals r
        on tmp.post_id=r.post_id
    ) tmp2
    group by action_date
) tmp3

简洁的写法
两层查询即可

SELECT
    ROUND(AVG(percent),2) average_daily_percent
FROM
    (
    SELECT
        action_date,
        COUNT(DISTINCT R.post_id) / COUNT(DISTINCT A.post_id) * 100 percent
    FROM 
        Actions A
    LEFT JOIN
        Removals R
    ON A.post_id = R.post_id
    WHERE
        extra='spam'
    GROUP BY
        action_date
    ) T

1158. 市场分析 I

请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。

注意时间判断要放到on里，如果放到where直接筛掉了 ID为3和4的情况 而放在on还会保留ID为3和4的情况且值为null

select user_id buyer_id , join_date,count(o.order_id) as orders_in_2019
from users u left join orders o 
on u.user_id=o.buyer_id and year(o.order_date)='2019'
group by u.user_id

1193. 每月交易 I

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额
不用子查询的方法

select date_format(trans_date,"%Y-%m") as month, country, count(*) trans_count,
sum(state='approved') approved_count, 
sum(amount) trans_total_amount, 
sum(case when state='approved' then amount else 0 end) approved_total_amount
from transactions
group by country, DATE_FORMAT(trans_date,"%Y-%m")

select month,country,count(state)  trans_count,count(a1)  approved_count,sum(amount) trans_total_amount,ifnull(sum(a1),0) approved_total_amount
from (
    select  substring(trans_date,1,7) as month, country , state    ,amount ,
    if (state='approved',amount,null) as a1
    from Transactions
) tmp
group by month,country

1194. 锦标赛优胜者

每组的获胜者是在组内得分最高的选手。如果平局，player_id 最小 的选手获胜。

select group_id,player_id
from(
    select group_id,tmp2.player_id ,row_number() over(partition by group_id order by s desc) rnk
    from (
        select player_id,sum(score) s from
        (select  first_player as player_id ,first_score as score 
        from Matches
        union all
        select second_player as player_id ,second_score as score 
        from Matches) tmp
        group by player_id
    )tmp2 join Players
    on tmp2.player_id=players.player_id
) tmp3
where rnk=1

简洁写法，分组会取第一行，所以在内层按照分数降序排序

select group_id, player_id
from (
    select players.*, sum(if(player_id = first_player, first_score, second_score)) score
    from players join matches
    on player_id = first_player or player_id = second_player
    group by player_id
    order by score desc, player_id
) tmp
group by group_id

1204. 最后一个能进入电梯的人

电梯最大载重量为 1000。

写一条 SQL 查询语句查找最后一个能进入电梯且不超过重量限制的 person_name 。题目确保队列中第一位的人可以进入电梯 。

自连接，巧妙分组

select a.person_name
from queue a, queue b
where a.turn >= b.turn
group by a.person_id
having sum(b.weight) <= 1000
order by a.turn desc
limit 1

select person_name
from(
    select *,
    lead(snk,1 ) over() l1
    from(
        select person_name,
        sum(weight) over (order by turn) snk
        from queue
    ) tmp
) tmp2
where (snk<=1000 and l1>1000) or(snk<=1000 and l1 is null) 

窗口函数的简单写法，不用再用lead，直接找到小于1000的最大值即可

select person_name 
from
(
    select person_name,turn,sum(weight)over(order by turn) as addup_weight
    from Queue a
) t where addup_weight<=1000
order by turn desc
LIMIT 1