WW在搜寻

分类

错题(1) 题解(59)

/ 注册

全部文章（共33篇）

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

#先找高难度平均分大于90的用户id /SELECT er.uid FROM exam_record er JOIN examination_info ei ON er.exam_id=ei.exam_id WHERE ei.tag='SQL' AND ei.difficulty='hard' GR...

Mysql

2022-02-12

0 258

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

SELECT * FROM (SELECT DISTINCT uid tid,'activity1' AS activity FROM exam_record WHERE YEAR(submit_time)=2021 AND score>=85 ) t1 UNION ALL SELECT * ...

Mysql

2022-02-09

0 230

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

SELECT* FROM ( SELECT exam_id,COUNT(DISTINCT uid) AS uv,COUNT(exam_id) AS pv FROM exam_record GROUP BY exam_id ORDER BY uv DESC,pv DESC) t1 UNION ALL ...

Mysql

2022-02-09

0 181

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

SELECT ui.level,COUNT(ui.uid) AS level_cnt FROM user_info ui JOIN exam_record er ON ui.uid=er.uid WHERE er.exam_id IN (SELECT exam_id FROM examinatio...

Mysql

2022-02-08

0 214

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

SELECT er.uid AS uid, SUM(IF(er.submit_time IS NULL,1,0)) AS incomplete_cnt, SUM(IF(er.submit_time IS NOT NULL,1,0)) AS complete_cnt, GROUP_CONCAT(DIS...

Mysql

2022-01-21

0 251

题解 | #统计作答次数#

SELECT DATE_FORMAT(submit_time,'%Y%m') AS submit_month, COUNT() AS month_q_cnt, ROUND( COUNT() / avg(DAY(LAST_DAY(submit_time)) ),3 ) AS avg_day_q_...

Mysql

2022-01-21

0 295

题解 | #统计作答次数#

#GROUP BY MONTH(submit_time); select DATE_FORMAT(submit_time,'%Y%m') as month, ROUND((count(distinct uid,DATE_FORMAT(submit_time,'%y%m%d')))/ count(di...

Mysql

2022-01-20

0 213

题解 | #统计作答次数#

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

Mysql

2022-01-19

0 266

题解 | #统计作答次数#

SELECT DISTINCT ei.tag,ei.difficulty,ROUND((SUM(er.score)-MAX(er.score)-MIN(er.score))/(COUNT(er.score)-2),1) clip_avg_score FROM examination_info ei...

Mysql

2022-01-19

0 203

题解 | #统计作答次数#

SELECT COUNT(start_time) total_pv,COUNT(submit_time) complete_pv,(SELECT COUNT(DISTINCT exam_id) FROM exam_record WHERE submit_time IS NOT NULL) ompl...

Mysql

2022-01-18

0 178