【SQL自学打卡|DAY17】——多表查询

前言

❤欢迎大家阅读我的文章呀❤
大家周末愉快呀！
希望你们在我的文章当中能有所收获！！！
SLogan:日拱一卒，功不唐捐！

知识点回顾

链接日期格式化

知识点：date_format() 通过这个函数匹配’%Y%m’年份和月份；

DATE_FORMAT(date,format)
date 参数是合法的日期。format 规定日期/时间的输出格式。

日期函数类——计算当前月的最后一天

LAST_DAY()

IF（）函数

IF(expr1,expr2,expr3)
如果expr1的值为true，则返回expr2的值
如果expr1的值为false，则返回expr3的值

concat（）函数

CONCAT函数用于将两个字符串连接起来，形成一个单一的字符串。

concat（str1, str2,…）

说明：返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

concat_ws（）函数

表示concat with separator，即有分隔符的字符串连接，可以指定分隔符（concat_ws就是concat with separator）

concat_ws（separator, str1, str2, …）

说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

例子：对于每条作答tag，用:连接日期和tag

concat_ws(‘:’, date(start_time), tag)

group_concat()函数

将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator] )

通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator分隔符是一个字符串值，缺省为一个逗号。

例子：对于一个人（组内）的多条作答，用;连接去重后的作答记录

group_concat(distinct concat_ws(‘:’, date(start_time), tag) SEPARATOR’;')

一、嵌套子查询

 
 
【SQL129】月均完成试卷数不小于3的用户爱作答的类别

解题思路：
  本道题目有非常多种解法，而我自己的思路是这样的：首先将两张表联结起来，然后根据题目的意思去寻找符合条件的用户，这里就需要用到子查询，本题的难点也就在这里，这里我用到了昨天我们用到过的IF（）函数，IF(submit_time,1,NULL)，如果有交卷时间，那么就代表完成了答题，为1，否非为0，然后求一个总数，再判断是否>=3，这就是子查询里面的内容。
代码如下：

SELECT tag,COUNT(tag) AS tag_cnt
FROM exam_record AS A
JOIN examination_info AS B
ON A.exam_id=B.exam_id
WHERE uid in(
    SELECT uid 
    FROM exam_record
    GROUP BY uid,MONTH(start_time)
    HAVING SUM(IF(submit_time,1,NULL))>=3
)
GROUP BY tag
ORDER BY tag_cnt DESC
1
2
3
4
5
6
7
8
9
10
11
12

结果：

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

解题思路：
这道题刚开始没什么问题，就是在第一个联结这里，有一个子查询，我没有考虑到的，还有就是当天作答这个条件，DATE_FORMAT(B.release_time,“%Y%m%d”)=DATE_FORMAT(C.start_time,“%Y%m%d”)，两张表里的两个时间要保证相同。
其他的地方注意细节不要弄错就行。
代码如下：

SELECT B.exam_id,COUNT(distinct C.uid) AS uv,ROUND(AVG(score),1) AS avg_score
FROM exam_record AS C
JOIN (
    SELECT exam_id,release_time
    FROM examination_info
    WHERE tag='SQL'
) AS B
ON C.exam_id=B.exam_id

JOIN user_info AS A 
ON A.uid=C.uid

WHERE A.level>5 AND DATE_FORMAT(B.release_time,"%Y%m%d")=DATE_FORMAT(C.start_time,"%Y%m%d")
GROUP BY B.exam_id
ORDER BY uv DESC ,avg_score

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

结果：

【SQL131】 作答试卷得分大于过80的人的用户等级分布

解题思路：
这道题并不难，只需要细心一点就行，题目的意思就是：
统计作答SQL类别的试卷得分大于过80的人的用户等级分布，按数量降序排序，相同数量按照等级降序
筛选条件就是>80和SQL 。
代码如下：

SELECT level,COUNT(distinct A.uid) AS level_cnt
FROM exam_record AS C 
JOIN  examination_info AS B
ON B.exam_id=C.exam_id
JOIN user_info AS A
ON A.uid=C.uid
WHERE  tag='SQL' AND score>80
GROUP BY level
ORDER BY level_cnt DESC,level DESC
1
2
3
4
5
6
7
8
9

结果：

二、合并查询

【SQL132】每个题目和每份试卷被作答的人数和次数

解题思路：
因为试卷和题目在不同的表中，而且两表中试卷和题目对应的字段名不同，所以需要使用到UNION，这里两个查询之间不需要去重，所以可以使用UNION ALL。

自定义排序

order by left/right(col_name,num) desc/asc

按col_name的左/右边第num个数进行降序/升序排序

正确代码如下：

SELECT exam_id AS tid,COUNT(DISTINCT uid) AS uv,COUNT(exam_id) AS pv
FROM exam_record
GROUP BY exam_id

UNION ALL 

SELECT question_id AS tid,COUNT(DISTINCT uid) AS uv,COUNT(question_id) AS pv
FROM practice_record
GROUP BY question_id

ORDER BY left(tid,1)desc,uv desc,pv desc
1
2
3
4
5
6
7
8
9
10
11

结果：

【SQL133】分别满足两个活动的人

解题思路：
输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出。本题需要将两个筛选合并，即用到UNION ALL

每次试卷得分都能到85分的人（activity1）

至少有一次用了一半时间就完成高难度试卷且分数大于80的人（activity2)，涉及到一个新的只是带你
timestampdiff(minute, B.start_time, B.submit_time) * 2 < A.duration

TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由interval 参数给出。

正确代码如下：

SELECT uid,'activity1' AS activity
FROM exam_record 
WHERE YEAR(submit_time)=2021
GROUP BY uid
HAVING MIN(score)>=85 
UNION ALL 
SELECT uid,'activity2' AS activity
FROM exam_record AS B
JOIN examination_info AS A
ON A.exam_id=B.exam_id
WHERE YEAR(B.submit_time)=2021 AND A.difficulty='hard'AND B.score>80 
AND timestampdiff(minute, B.start_time, B.submit_time) * 2 < A.duration

ORDER BY uid

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

结果：

 

总结

  今天是【SQL进阶挑战】有关于多表查询专项练习和分享！还有两道连接查询没做，这两题有点难度，明天再慢慢思考吧！
  周末啦，睡个懒觉再继续卷吧
  一键三连呀，谢谢！！！
  感谢您宝贵的阅读，关注和点赞噢！晚安❤