冷凡社长

分类

题解(2)

/ 注册

TA的专栏 0篇文章 0人订阅

0篇文章 0人学习

全部文章（共133篇）

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

要求 1、高难度 SQL 试卷的得分平均值大于80 且用户等级为7difficulty = "hard" and t.level = 7 and tag = 'SQL'group by uid having avg(score)>80 2、年份要求202...

Mysql

2022-08-22

7 404

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

select uid,'activity1' as activity from exam_record where year(start_time)=2021 group by uid having avg(score) >=85 union all select uid,&...

Mysql

2022-08-19

1 298

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

select t.tid , count(distinct uid) as uv, count(*) from ( select uid,exam_id as tid from exam_record union select uid,question_id from practice_recor...

Mysql

2022-08-19

0 245

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

select t2.level ,count( t.uid) as level_cnt from exam_record t inner join examination_info t1 on t.exam_id =t1.exam_id inner join user_info t2 using(...

Mysql

2022-08-19

1 288

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

类别 SQL发布当天用户为五级以上 select t.exam_id ,count(distinct t.uid) as uv ,round(avg(t.score),1) as avg_score from exam_record t inner join examination_info t1...

Mysql

2022-08-19

1 279

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

select tag,count(*) as tag_cnt from exam_record t join examination_info t1 using(exam_id)where t.uid in (select uid from exam_record gr...

Mysql

2022-08-19

1 264

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

with t2 as ( select t.*,t1.tag from exam_record t inner join examination_info t1 using(exam_id) where year(start_time)=2021 ) select uid,c...

Mysql

2022-08-19

1 302

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

这道题主要是增加了一个汇总项，通常我们不会在SQL语句中增加汇总项，所以这道题多少有点画蛇添足。这个题想完整的写出来需要蛮多知识点和测试比较核心的是知道LAST_DAY是返回每月最后一天的意思，加入day,可以知道这个月有多少天，max本来可以不加，因为有了汇总项目，不加的话汇总项的日期可能就...

Mysql

2022-08-18

3 331

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

select DATE_FORMAT(submit_time,'%Y%m') as month ,round(count(DISTINCT uid,DATE_FORMAT(submit_time,'%Y%m%d'))/count(DISTINCT uid),2)as ...

Mysql

2022-08-18

1 228

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

解题点求出SQL试题的平均分，然后让分数与其比较，然后找出最小值。思路点，知道子查询可以用在where 中的运算判断中。 with t2 as (select t.* from exam_record t join examination_info t1 using(exam_id) whe...

Mysql

2022-08-18

1 252