网站 专题建设服务app001推广平台官网

1194. 锦标赛优胜者–做出来了

题型：看题
解答：先处理matches表，整出分数列和players表连接



注意点： union all 时，会存在一个 player_id有多行记录，这样再和p表连接时，会变多，所以需要先group by聚合一下

with t as
(select player_id,sum(score) as score
from (select first_player player_id,first_score scorefrom Matchesunion allselect second_player player_id,second_score scorefrom Matches)agroup by player_id
)select distinct a.group_id,a.player_id
from
(select p.player_id,p.group_id,rank()over(partition by p.group_id order by ifnull(t.score,0) desc, p.player_id asc) as r
from Players p left join t on p.player_id=t.player_id) a
where r=1

窗口函数总结：
<窗口函数> over (partition by <分组列名> order by <排序列名>)

rank() : 阶梯排序-前两个是并列的1，接下来就是第3名
dense_rank(): 连续排序-前两个是并列的1，接下来就是第2名
row_number(): 不会出现重复的排序

1225. 报告系统状态的连续日期-勉强

题型：连续天数问题

select type as period_state, min(date) as start_date, max(date) as end_date
from
(select type, date, subdate(date,row_number()over(partition by type order by date)) as difffrom(select 'failed' as type, fail_date as date from Failedunion allselect 'succeeded' as type, success_date as date from Succeeded) a
)a
where date between '2019-01-01' and '2019-12-31'
group by type,diff
order by start_date

1159. 市场分析 II

select user_id as seller_id,
if(t.item_brand=u.favorite_brand,'yes','no') 2nd_item_fav_brand
from Users u
left join (select o.item_id, i.item_brand, seller_id,rank() over (partition by seller_id order by order_date) rk from orders o left join items i on o.item_id=i.item_id) t
on u.user_id=t.seller_id and rk=2

1205. 每月交易II

with cte as
(select * from transactionsunion allselect id, country, 'chargeback' state, amount, c.trans_datefrom chargebacks c left join transactions t on c.trans_id = t.id
) select date_format(trans_date, '%Y-%m') month, country,sum(state = 'approved') approved_count,sum(if(state = 'approved', amount, 0)) approved_amount,sum(state = 'chargeback') chargeback_count,sum(if(state = 'chargeback', amount, 0)) chargeback_amount
from cte 
group by month, country
having approved_amount or chargeback_amount