无上清颜

题解

/ 注册

全部文章 / 题解（共49篇）

题解 | #操作符混合运用#

select device_id,gender,age,university,gpa from user_profile where (university,gpa) in (select university,gpa from user_profile where university='山东大学...

Mysql

2021-12-02

12 1678

题解 | 本题难点：Exists

-- 第一步做出所有人的两个指标 with t as ( select t.uid,level,sum(start_time is not null and submit_time is null) as incomplete_cnt, round(sum(start_t...

Mysql

2021-12-02

28 1416

题解 | 正则表达式

select uid,exam_id,round(avg(score),0) as _score from exam_record where exam_id in (select exam_id from examination_info where tag like "c%") a...

Mysql

2021-12-01

1 417

题解 |试试三表左连

with t as ( select t.uid, t.nick_name, t.achievement, t1.start_time, t2.submit_time from user_info t join exam_record t1 ...

Mysql

2021-11-30

9 1341

题解 | #0级用户高难度试卷的平均用时和平均得分#

select uid, round(sum(if(score is not null,score,0))/count(1),0) as avg_score, round(sum(if(score is NULL,duration,timestampdiff(minute,...

Mysql

2021-11-30

1 313

题解 |

select exam_id,sum(if(score is null,1,0)) as incomplete_cnt,round(sum(if(score is null,1,0))/count(1),3) as incomplete_rate from exam_record group by ...

Mysql

2021-11-29

1 380

题解 | #对试卷得分做min-max归一化#

select *,sum(month_cnt)over(partition by exam_id order by start_month) as cum_exam_cnt from (select exam_id,date_format(start_time,"%Y%m") as start_m...

Mysql

2021-11-26

2 409

题解 | #对试卷得分做min-max归一化#

with t as ( select uid,exam_id, if(round((score-min(score)over(partition by exam_id))/(max(score)over(partition by exam_id)-min(score)over(pa...

Mysql

2021-11-26

9 1226

题解 | #未完成率较高的50%用户近三个月答卷情况#

难度没想象中那么大 -- 第一步：过渡表，做出2020年和2021年不同tag的做完次数和做完次数排名 with t AS ( select *,RANK()over(order by exam_cnt desc) as exam_cnt_rank from(select tag,y...

Mysql

2021-11-26

1 412

题解 | #未完成率较高的50%用户近三个月答卷情况#

-- 第一步：筛选出SQL试卷未完成率较高的50%的用户 with t as ( select uid from (select *,row_number()over(order by incomplete_rate desc) as rn,count(1)over() ...

Mysql

2021-11-25

8 735