〇、内容

时间比较2-2

联合结果各自排序

查询列和GROUP BY

一、嵌套子查询

1、月均完成试卷数不小于3的用户爱作答的类别

自己的答案【错误】：

SELECT tag,

    COUNT(A.start_time) AS tag_cnt

FROM (

    -- 查询 “当月均完成试卷数”不小于3的用户们

    SELECT *

    FROM exam_record

    GROUP BY uid

    HAVING COUNT(*)>=3

) A

RIGHT JOIN examination_info B

ON A.exam_id=B.exam_id

GROUP BY tag

ORDER BY tag_cnt DESC

答案：【group by的字段一定要出现在查询列中，*不算】

SELECT tag,

    COUNT(B.tag) AS tag_cnt

FROM exam_record A

RIGHT JOIN examination_info B

ON A.exam_id=B.exam_id

WHERE uid IN (

    SELECT uid

    FROM exam_record

    GROUP BY uid

    -- 统计当前用户完成试卷总数

    -- 统计该用户有完成试卷的月份数

    HAVING COUNT(submit_time) / COUNT(DISTINCT DATE_FORMAT(submit_time, "%Y%m")) >= 3

)

GROUP BY tag

ORDER BY tag_cnt DESC

2、试卷发布当天作答人数和平均分【☆】

自己的答案【错误】

SELECT

    exam_id,

    SUM(IF(DATE_FORMAT((submit_time,"%Y%m")==DATE_FORMAT(release_time,"%Y%m") and level>=5,1,0))) AS uv,

    ROUND(AVG(score),1) AS avg_score

FROM user_info A

JOIN examination_info

JOIN exam_record

ON

    A.uid=C.uid

    AND

    B.exam_id=C.exam_id

WHERE

    tag="SQL"

GROUP BY exam_id

ORDER BY uv DESC,avg_score ASC

正确答案：【判断相等用一个等号】

SELECT

    C.exam_id,

    COUNT(DISTINCT C.uid) AS uv,

    ROUND(AVG(score),1) AS avg_score

FROM user_info A

JOIN examination_info B

JOIN exam_record C

ON

    A.uid=C.uid

    AND

    B.exam_id=C.exam_id

WHERE

    tag="SQL"

    AND

    level>5

    AND

    DATE(submit_time)=DATE(release_time)

GROUP BY C.exam_id

ORDER BY uv DESC,avg_score ASC

3、作答试卷得分大于过80的人的用户等级分布

SELECT

    level,

    COUNT(*) AS level_cnt

FROM user_info A

JOIN examination_info B

JOIN exam_record C

-- NATURAL/FULL/CROSS

ON A.uid=C.uid

AND B.exam_id=C.exam_id

WHERE

    tag="SQL"

    AND

    score>80

GROUP BY level

ORDER BY level_cnt DESC

二、合并查询

1、每个题目和每份试卷被作答的人数和次数

答案：【UNION和ORDER BY混用会被覆盖】

-- order by可以存在 union的字句里面，但是功能不会生效

SELECT * FROM

(SELECT

    exam_id AS tid,

    COUNT(DISTINCT uid) AS uv,

    COUNT(exam_id) AS pv

FROM exam_record

GROUP BY tid

ORDER BY uv DESC,pv DESC) AS A

UNION

SELECT * FROM

(SELECT

    question_id AS tid,

    COUNT(DISTINCT uid) AS uv,

    COUNT(question_id) AS pv

FROM practice_record

GROUP BY tid

ORDER BY uv DESC,pv DESC) AS B

2、分别满足两个活动的人

思路：TIMESTAMPDIFF(SECOND,start_time,submit_time)<=duration*30，时间比较用SECOND和TIMESTAMPDIFF

至少有一次不需要聚合函数，在where中即可实现，先查找出符合要求的，再进行分组

全部需要用聚合函数，先分组再having

-- 输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次

-- 用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出。

-- 全部成绩大于85可以用最小成绩>85表示

(SELECT

    uid,

    "activity1" AS activity

FROM exam_record

GROUP BY uid

HAVING MIN(score)>=85)

UNION ALL

(SELECT

    uid,

    "activity2" AS activity

FROM exam_record B

LEFT JOIN examination_info A

ON A.exam_id=B.exam_id

WHERE

    TIMESTAMPDIFF(SECOND,start_time,submit_time)<=duration*30

    AND

    score>=80

    AND

    difficulty="hard"

GROUP BY uid)

ORDER BY uid

三、连接查询

1、满足条件的用户的试卷完成数和题目练习数

自己的写法【错误】

SELECT

    A.uid,

    COUNT(DISTINCT C.uid) AS exam_cnt,

    COUNT(DISTINCT D.uid) AS question_cnt

FROM user_info A

JOIN examination_info B

JOIN exam_record C

JOIN practice_record D

ON

    A.uid=C.uid

    AND

    A.uid=D.uid

    AND

    B.exam_id=C.exam_id

WHERE

    level=7

    AND

    tag="SQL"

    AND

    difficulty="hard"

    AND

    (YEAR(C.submit_time)=2021

    OR

    YEAR(D.submit_time)=2021)

GROUP BY A.uid

HAVING -- 子句不能出现year

    AVG(C.score)>80

ORDER BY exam_cnt ASC,question_cnt DESC

答案：

-- 先分别写出2021年分组后的试卷完成情况和题目练习情况

-- 再查询出高难度SQL试卷得分平均值大于80并且是7级的红名大佬

select

    uid,

    exam_cnt,

    if(question_cnt is null, 0, question_cnt)

from

(select

    uid,

    count(submit_time) as exam_cnt

from exam_record

where YEAR(submit_time) = 2021

group by uid) t

left join

(select

    uid,

    count(submit_time) as question_cnt

from practice_record

where YEAR(submit_time) = 2021

group by uid) t2 using(uid)

where uid in

(

select

    uid

from exam_record

join examination_info using(exam_id)

join user_info using(uid)

where tag = 'SQL' and difficulty = 'hard' and `level` = 7

group by uid

having avg(score) >= 80

)

order by exam_cnt asc, question_cnt desc

2、每个6/7级用户活跃情况

自己分步写的：

-- 查询出6/7级用户

SELECT

    uid

FROM user_info

WHERE

    level=6

    OR

    level=7

-- 查询总活跃月份数

SELECT

    uid,

    COUNT(DISTINCT act_month) AS act_month_total

FROM

(SELECT

    uid,

    DATE_FORMAT(start_time,"%Y%m") AS act_month

FROM exam_record

UNION ALL

SELECT

    uid,

    DATE_FORMAT(submit_time,"%Y%m") AS act_month

FROM practice_record) t1

GROUP BY uid

-- 查询2021年活跃天数（UNION？）

SELECT

    uid,

    COUNT(DISTINCT act_days) AS act_days_2021

FROM

((SELECT

    uid,

    DATE(start_time) AS act_days

FROM exam_record

WHERE

    YEAR(start_time)=2021)

UNION ALL

(SELECT

    uid,

    DATE(submit_time) AS act_days

FROM practice_record

WHERE

    YEAR(submit_time)=2021)) t1

GROUP BY uid

-- 查询试卷作答活跃天数

SELECT

    uid,

    COUNT(DISTINCT DATE(start_time)) AS act_month_total

FROM exam_record

WHERE YEAR(start_time)=2021

GROUP BY uid 

-- 2021年答题活跃天数

SELECT

    uid,

    COUNT(DISTINCT DATE(submit_time)) AS act_month_total

FROM practice_record

WHERE YEAR(submit_time)=2021

GROUP BY uid

答案：

select

    ui.uid,

    count(distinct left(s,6)) as act_month_total,

    count(distinct if(left(s,4)='2021',right(s,4),null)) as act_days_2021,

    count(distinct if(left(s,4)='2021' and tag='e',right(s,4),null)) as act_days_2021_exam,

    count(distinct if(left(s,4)='2021' and tag='p',right(s,4),null)) as act_days_2021_question

from (

        select uid,DATE_FORMAT(submit_time,'%Y%m%d') as s,'p' tag from practice_record pr

        union all

        SELECT uid,DATE_FORMAT(start_time,'%Y%m%d') as s,'e' as tag from exam_record er

)mon

right join user_info ui

on ui.uid = mon.uid

where ui.level >5

group by uid

order by act_month_total DESC,act_days_2021 desc

【SQL进阶】【分步写、联合各自排序、TIMESTAMPDIFF时间比较】Day04：多表查询的相关教程结束。