人生苦短，但求成长

题解

/ 注册

全部文章 / 题解（共86篇）

题解 | #国庆期间每类视频点赞量和转发量#

这题还是有点难度啊，可以用窗口函数里面的滑动窗口解决 rows between current row and 6 following 表示取当前行和下6行 select * from ( &...

Mysql

2022-03-17

1 479

题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#

select author,month, round((follow_cn - unfollow_cn)/play_times,3) as fans_growth_r...

Mysql

2022-03-17

1 366

题解 | #每类视频近一个月的转发量/率#

select tag,sum(if(if_retweet=1,1,0)) as retweet_cut, round(sum(if(if_retweet=1,1,0))/count(1)...

Mysql

2022-03-17

1 355

题解 | #平均播放进度大于60%的视频类别#

select tag, concat(round(avg(if(timestampdiff(second,start_time,end_time) >= duration,1,ti...

Mysql

2022-03-16

2 380

题解 | #各个视频的平均完播率#

select t1.video_id, round(sum(if(timestampdiff(second,start_time,end_time) >= duration,1,0...

Mysql

2022-03-16

1 409

题解 | #大小写混乱时的筛选统计#

要用满足完成数小于3的小写tag去匹配对应大写tag的数量！！！醉了 select t1.tag,t2.answer_cnt from ( &n...

Mysql

2022-03-16

7 443

题解 | #对过长的昵称截取处理#

select uid,if(char_length(nick_name) > 13,concat(substr(nick_name,1,10),'...'),nick_name) as nick_name from user_info...

Mysql

2022-03-16

1 396

题解 | #修复串列了的记录#

select exam_id,substring_index(tag,',',1) as tag, substring_index(substring_index(tag,',',2),...

Mysql

2022-03-16

1 363

题解 | #注册当天就完成了试卷的名单第三页#

select t1.uid,level,register_time,max(score) as max_score from user_info t1 join exam_record t2 on t1.uid...

Mysql

2022-03-16

9 394

题解 | #注册时间最早的三个人#

select uid,nick_name,register_time from ( select uid,nick_name,register_time, &nb...

Mysql

2022-03-16

4 671