jjdhfbu

分类

/ 注册

全部文章（共44篇）

题解 | #满足条件的用户的试卷完成数和题目练习数#

# #高难度SQL试卷得分平均值大于80并且是7级的红名大佬 # #统计2021年的试卷完成次数 # #count(*) as exam_cnt # select uid,count(*) as exam_cnt # from exam_record as er # join examinatio...

2023-04-03

0 305

题解 | #分别满足两个活动的人#

#activity1要求每次考试分数都要85以上，若最低的为85，则后面每次都会比他高 select uid,"activity1" as activity from exam_record where year(submit_time)="2021" group by uid having min...

2023-04-03

0 305

题解 | #每个题目和每份试卷被作答的人数和次数#

select exam_id as tid,count(distinct exam_record.uid) as uv, count(*) as pv from exam_record group by exam_id union select question_id as tid,count(d...

2023-04-02

0 197

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

#对比前两条确实大巫见小巫，简单多了，前两条最怕是连题目都没读清 select level,count(distinct ui.uid) as level_cnt from exam_record as er join user_info as ui on er.uid=ui.uid join ex...

2023-04-02

0 255

题解 | #得分不小于平均分的最低分#

select min(score) as min_score_over_avg from exam_record as er left join examination_info as ei on er.exam_id=ei.exam_id where ei.tag="SQL" and score...

2023-03-31

0 253

题解 | #统计作答次数#

select count(*) as total_pv, count(submit_time) as complete_pv, count(distinct if(submit_time is null,exam_id,null)) as complete_exam_cnt from exam_re...

2023-03-31

0 248

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

select tag,difficulty, round((sum(score)-max(score)-min(score))/(count(score)-2),1) as clip_avg_score from examination_info as ei join exam_record as ...

2023-03-30

0 313

题解 | #创建索引#

create INDEX idx_duration on examination_info(duration); create UNIQUE INDEX uniq_idx_exam_id on examination_info(exam_id); create FULLTEXT INDEX full...

2023-03-30

0 241

题解 | #修改表#

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-03-30

0 252

题解 | #创建一张新表#

# create table if not exists user_info_vip ( # id int primary key auto_increment comment "自增ID", # uid int unique not null comment "用户ID", # ...

2023-03-30

0 308