JoKerhhh

分类

/ 注册

全部文章（共34篇）

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

select tag, COUNT(*) AS tag_cnt FROM exam_record AS er JOIN examination_info AS ei using(exam_id) #要对uid进行限制当月均完成试卷数不小于3的用户 WHERE uid in ( select ...

2024-01-27

0 169

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

select uid, COUNT(start_time) - COUNT(submit_time) AS incomplete_cnt, COUNT(submit_time) AS complete_cnt, #SUM(if(submit_time is NULL,1,0)) AS incomp...

2024-01-27

0 183

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

select date_format(submit_time,'%Y%m') AS submit_month, COUNT(*) AS month_q_cnt, ROUND(COUNT(*) / DAY(LAST_DAY(MIN(submit_time))),3) AS avg_day_q_cnt ...

2024-01-27

0 179

题解 | #平均活跃天数和月活人数#

# select CONCAT(YEAR(start_time), LPAD(MONTH(start_time), 2, '0')) AS month, select date_format(submit_time, '%Y%m') AS month, ROUND(COUNT(distinct ui...

2024-01-24

0 194

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

select MIN(score) AS min_score_over_avg FROM exam_record AS er JOIN examination_info AS ei using (exam_id) WHERE ei.tag = 'SQL' AND score >= ( ...

2024-01-23

0 190

题解 | #统计作答次数#

select COUNT(*) AS total_pv, COUNT(submit_time) AS complete_pv, COUNT(DISTINCT CASE WHEN submit_time IS NOT NULL THEN exam_id END) AS complete_exam_cn...

2024-01-23

0 223

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

WITH ExamInfo AS ( SELECT exam_id,tag,difficulty FROM examination_info WHERE tag = 'SQL' AND difficulty = 'hard' ) SELECT ei.tag, ...

2024-01-23

0 188

题解 | #删除索引#

DROP INDEX uniq_idx_exam_id ON examination_info; DROP INDEX full_idx_tag ON examination_info;

2024-01-22

0 150

题解 | #创建索引#

CREATE INDEX idx_duration ON examination_info (duration); CREATE unique INDEX uniq_idx_exam_id ON examination_info (exam_id); #CREATE fulltext INDEX f...

2024-01-22

0 132

题解 | #删除表#

DROP TABLE IF EXISTS exam_record_2011,exam_record_2012, exam_record_2013,exam_record_2014;

2024-01-22

0 130