牛客987852806号

分类

题解(45)

/ 注册

全部文章（共163篇）

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

delete from exam_record where id in ( select t.id from ( select *,ROW_NUMBER()over(...

Mysql

2021-11-02

0 364

题解 | #第二快/慢用时之差大于试卷时长一半的试卷#

SELECT t1.exam_id, t1.duration, t1.release_time from( select t.exam_id, t.duration, t.release_time, sum(case when t.m=2 then diff&...

Mysql

2021-10-28

0 483

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

SELECT t.tag, t.uid, t.m from( select e2.tag, e1.uid, dense_rank()over(partition by e2.tag order by max(e1.score) ...

Mysql

2021-10-28

0 441

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

SELECT uid, 'activity1' as activity from exam_record WHERE left(start_time,4)='2021' #第一问答案 group by&nb...

Mysql

2021-10-28

0 465

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

select u1.level, count(u1.level) cnt from exam_record e1 left join examination_info e2 on e1.exam_id=e2.exam_id left join user_info u1 on u1.uid=e1.u...

Mysql

2021-10-28

0 378

题解 | #试卷发布当天作答人数和平均分#

SELECT e2.exam_id,count(distinct e2.uid) #试题有几个人完成了 as cnt,round(avg(e2.score),1) #试题完成平均分 as avgscore from examination_info e1 left join exam_...

Mysql

2021-10-27

0 454

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

select e2.tag, count(*) cnt from exam_record e1 left join examination_info e2 on e1.exam_id=e2.exam_id WHERE e1.uid in ( select uid from exam_rec...

Mysql

2021-10-27

2 427

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

select e1.uid #提取用户ID, sum(case when e1.submit_time is null then 1 else 0 end) inp #未完成的次数，当提交时间为空，就...

Mysql

2021-10-27

8 772

题解 | #统计作答次数#

select count(uid),#问多少人来参与这次作答人数，有多少UID就有多少人 sum(case when submit_time is null then 0 else 1 end ) #统计多少人提交了做完了，求利sum()+case判断,当submit_time 是空等于0，不是空等...

Mysql

2021-10-27

5 867

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

select t.tag, t.difficulty, round( avg(t.score),1) from ( select e1.tag, e1.difficulty, e2.score, max(e2.score)over(partition by e2.exam_id ) as m, m...

Mysql

2021-10-27

2 643