念_长征

分类

/ 注册

全部文章（共166篇）

题解 | #作答试卷得分大于过80的人的用户等级分布#

# 查询所有有过作答SQL类别试卷得分大于80记录的用户等级分布 select level, count(distinct ui.uid) level_cnt from user_info ui join exam_record er on ui.uid=er.uid join examinatio...

2024-01-22

0 149

题解 | #月均完成试卷数不小于3的用户爱作答的类别#

# 查询有某个月份完成试卷数不小于3的所有用户的id select distinct uid from( select uid, date_format(submit_time, '%Y%m') as fin_month from exam_record where subm...

2024-01-21

0 178

题解 | #月总刷题数和日均刷题数#

# 使用day(last_day())来计算某月的天数 with t1 as (select date_format(submit_time,'%Y%m') as submit_month, count(*) as month_q_cnt, round(count(*)/day(last_day...

2024-01-21

0 236

题解 | #试卷完成数及增长率--窗口函数、格式转换函数

# 统计每类试卷在2020年和2021年上半年的做完次数及做完次数排名 select tag, sum(if(submit_time < '2020-07-01 00:00:00' and year(submit_time) = '2020', 1,0)) as exam_cnt_20, su...

2024-01-14

1 206

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

select uid, case when char_length(nick_name)>13 then concat(substr(nick_name, 1, 10), '...') else nick_name end as nick_name from user_info where c...

2024-01-11

0 197

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

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

2024-01-11

0 176

题解 | #删除表#

drop table if exists exam_record_2011,exam_record_2012,exam_record_2013,exam_record_2014; 直接这样明了地枚举出需要删除的表；有想过利用存储过程和while...do实现循环删除，但是在变量放到drop被删除的表...

2024-01-11

0 185

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

select ui.uid, level, register_time, max(score) max_score from user_info ui left join exam_record er on ui.uid = er.uid where ui.uid in ( select u...

2024-01-08

0 200

题解 | #未完成试卷数大于1的有效用户#

select uid, count(if(submit_time is null,1,null)) incomplete_cnt, count(submit_time) complete_cnt, group_concat(distinct detail_i separator ';') detai...

2024-01-03

0 234

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

# 限量查询又可称分页查询 select uid, nick_name, register_time from user_info order by register_time limit 3;

2024-01-02

0 197