mysql 代替 row_number

最新推荐文章于 2024-02-02 14:30:00 发布

乱写乱画

最新推荐文章于 2024-02-02 14:30:00 发布

阅读量696

点赞数

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/qq_41802773/article/details/122925163

版权

博客展示了一段MySQL代码，用于查询虎牙直播相关数据。代码通过设置变量、子查询和连接操作，从不同表中筛选出特定日期的游戏名称、直播间数量、总计数和弹幕数等信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

set @rank:=0;
select "huya" as PT, T1.game_name, T1.profileRoom, T2.totalCount, T3.DM from (
select count(DISTINCT profileRoom) as profileRoom,game_name from huyaLiveInfo where date_format(insert_date, '%Y-%m-%d') = "2022-01-24" group by game_name
) T1
LEFT JOIN(
select game_name, sum(totalCount) as totalCount from(
select *,@rank:=case when @current_id<>profileRoom then 1 else @rank+1 end as rank,@current_id:=profileRoom
from huyaLiveInfo where date_format(insert_date, '%Y-%m-%d') = "2022-01-22"
order by totalCount desc
) a
where a.rank = 1
group by a.game_name
) T2
on T1.game_name = T2.game_name
LEFT JOIN(
select count(*) as DM, game_name from huyaLiveDanMu_copy where date_format(insert_date, '%Y-%m-%d') = "2022-01-24" group by game_name
) T3
on T3.game_name = T2.game_name