念_长征

分类

/ 注册

全部文章（共166篇）

题解 | #统计有未完成状态的试卷的未完成数和未完成率#

# 查询出有未完成状态的试卷id select distinct exam_id from exam_record where submit_time is null # 查询有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate select exam_i...

2024-01-02

0 202

题解 | #近三个月未完成试卷数为0的用户完成情况#

# 查询各用户每个月的试卷作答记录数和试卷完成数以及每个月的时间排序 select uid, date_format(start_time, '%Y%m') s_month, count(start_time) s_num, count(submit_time) f_num, row_number...

2024-01-02

0 189

题解 | #每个6/7级用户活跃情况#--多个子查询表连接

# 合并试卷作答记录表和题目练习表，并用类型标签标注所属类型 with k1 as( select uid, exam_id, start_time, date_format(start_time, '%Y%m') start_month, date_format(start_tim...

2024-01-02

0 170

题解 | #创建一张新表#

create table if not exists user_info_vip( id int(11) not null primary key auto_increment comment '自增ID', uid int(11) not null unique comment '...

2024-01-02

0 162

题解 | #连续两次作答试卷的最大时间窗#

# 查询在2021年各用户作答试卷的日期、及每天作答的试卷数目 select uid, date_format(start_time, '%Y%m%d') start_date, count(start_time) fin_num from exam_record where year(start_...

2024-01-01

0 216

题解 | #每类试卷得分前3名#

# 查询各用户在每类试卷中的最大分数和最小分数 select uid, tag, max(score) max_score, min(score) min_score from exam_record er join examination_info ei on er.exam_id=ei.exam...

2023-12-30

0 178

题解 | #SQL类别高难度试卷得分的截断平均值#

# 查询用户完成SQL类别高难度试卷的所有得分情况，并制造不重复排序列 select score, row_number()over(order by score) as rank_score from exam_record er join examination_info ei on ei.ex...

2023-12-30

0 182

题解 | #各用户等级的不同得分表现占比#

-------------------完整代码-------------------- select level, score_grade, round(defen_num/(sum(defen_num)over(partition by level)),3) as ratio from( ...

2023-12-28

0 191

题解 | 修改表 -添加列到指定位置、修改列的名称和默认值

alter table user_info add column school VARCHAR(15) after level; alter table user_info change column job profession varchar(10); alter table user_info...

2023-12-28

0 171

题解 | #删除记录（二）#

delete from exam_record where submit_time is null or timestampdiff(minute, start_time, submit_time) < 5 order by start_time limit 3;

2023-12-28

0 167