【MySQL】【牛客-SQL进阶挑战】04 多表查询

---

SQL题解汇总

https://blog.csdn.net/guliguliguliguli/article/details/126109166

题目链接

https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=240

牛客-SQL进阶挑战 04 多表查询

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

SELECT tag, COUNT(t1.exam_id) tag_cnt
FROM examination_info t1
JOIN exam_record t2 ON t1.exam_id = t2.exam_id
WHERE uid IN (
    SELECT uid
    FROM exam_record
    WHERE submit_time IS NOT NULL 
    	AND DATE_FORMAT(submit_time,'%Y%m')='202109'
    GROUP BY uid
    HAVING COUNT(uid) >= 3
)
GROUP BY t1.exam_id
ORDER BY tag_cnt DESC;

SQL 130 试卷发布当天作答人数和平均分

SELECT 
    t1.exam_id, 
    COUNT(DISTINCT t1.uid) uv, 
    ROUND(AVG(t1.score),1) avg_score 
FROM exam_record t1 
JOIN (SELECT exam_id, DATE(release_time) rt
    FROM examination_info
    WHERE tag = 'SQL') t2
ON t1.exam_id = t2.exam_id
JOIN (SELECT uid
    FROM user_info
    WHERE `level` > 5) t3 
ON t1.uid = t3.uid
WHERE 
    t1.submit_time IS NOT NULL 
    AND DATE(t1.submit_time) = rt
GROUP BY t1.exam_id
ORDER BY uv DESC, avg_score ASC;

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

SELECT
  t3.` level `,
  COUNT(DISTINCT t1.uid) level_cnt
FROM
  exam_record t1
  JOIN examination_info t2 ON t1.exam_id = t2.exam_id
  JOIN user_info t3 ON t3.uid = t1.uid
WHERE
  score > 80
  AND tag = 'SQL'
GROUP BY
  t3.` level `
ORDER BY
  level_cnt DESC,
  ` level ` DESC;

SQL 132 每个题目和每份试卷被作答的人数和次数

SELECT * FROM (SELECT exam_id tid, COUNT(DISTINCT uid) uv, COUNT(exam_id) pv
FROM exam_record
GROUP BY exam_id
ORDER BY uv DESC, pv DESC) t1

UNION ALL

SELECT * FROM (SELECT question_id tid, COUNT(DISTINCT uid) uv, COUNT(question_id) pv
FROM practice_record
GROUP BY question_id
ORDER BY uv DESC, pv DESC) t2;

SQL 133 分别满足两个活动的人

SELECT DISTINCT uid,'activity1' activity
FROM exam_record
WHERE YEAR(submit_time) = 2021
GROUP BY uid
HAVING COUNT(uid) = SUM(IF(score>=85,1,NULL))

UNION ALL

SELECT DISTINCT uid,'activity2' activity
FROM exam_record t1 
JOIN examination_info t2 ON t1.exam_id = t2.exam_id
WHERE
    score > 80
    AND YEAR(start_time) = 2021
    AND difficulty = 'hard'
    AND TIMESTAMPDIFF(MINUTE,start_time,submit_time)<(duration/2)
ORDER BY uid;

SQL 134 满足条件的用户的试卷完成数和题目练习数（困难）

先找出满足条件的uid，再通过uid，在exam_record和practice_record中查找符合条件的记录，最后将三张表连接起来

SELECT t1.uid, exam_cnt, IFNULL(question_cnt,0) question_cnt
FROM(
    SELECT t1.uid
    FROM exam_record t1
    JOIN user_info t2 ON t1.uid = t2.uid
    JOIN examination_info t3 ON t1.exam_id = t3.exam_id
    WHERE t3.tag = 'SQL' AND t3.difficulty = 'hard' AND t2.level = 7
    GROUP BY t1.uid
    HAVING AVG(t1.score) > 80
) t1
LEFT JOIN (
    SELECT uid, COUNT(exam_id) exam_cnt
    FROM exam_record
    WHERE YEAR(submit_time) = 2021
    GROUP BY uid
) t2 ON t1.uid = t2.uid
LEFT JOIN (
    SELECT uid,COUNT(question_id) question_cnt
    FROM practice_record
    WHERE YEAR(submit_time) = 2021
    GROUP BY uid
) t3 ON t1.uid = t3.uid
ORDER BY exam_cnt ASC,question_cnt DESC;

SQL 135 每个6/7级用户活跃情况（困难）【详解】

题目主要内容

需要统计以下内容：

• 每个6/7级用户总活跃月份数
• 每个6/7级用户2021年活跃天数
• 每个6/7级用户2021年试卷作答活跃天数
• 每个6/7级用户2021年答题活跃天数

按照总活跃月份数、2021年活跃天数降序排序

问题拆分

exam_record表和user_info表连接，注意是外连接，选取level是6或者7的用户，提取出表的月份（如202109）和日期（20210801），再另外加上一个tag列，用字符串exam填充，表明这些数据是从exam_record表中提取出来的

question_record表和user_info表连接，注意是外连接选取level是6或者7的用户，提取出表的月份（如202109）和日期（20210801），再另外加上一个tag列，用字符串question填充，表明这些数据是从question_record表中提取出来的

Q：为什么是外连接？
A：根据题目所给实例，可以看出，1003是level为7的用户，但是他没有任何活跃，但也显示在了最后的结果表中

将上面的两个表用UNION ALL连接起来（纵向上连接），变成一张总表，里面包含了全部的日期信息，以及具体到某一条数据是来自exam_record还是question_record都可以分辨出来，方便统计

最后，按照题目要求，写出最外层SELECT部分具体内容即可

答案

SELECT uid,
	COUNT(DISTINCT act_month) act_month_total,
	COUNT(DISTINCT IF(YEAR(act_date)=2021,act_date,NULL)) act_days_2021,
	COUNT(DISTINCT IF(YEAR(act_date)=2021 AND tag='exam',act_date,NULL)) act_days_2021_exam	,
	COUNT(DISTINCT IF(YEAR(act_date)=2021 AND tag='question',act_date,NULL)) act_days_2021_question
FROM (
	SELECT t1.uid,
		DATE_FORMAT(start_time,'%Y%m') act_month,
		DATE(start_time) act_date,
		'exam' tag
	FROM user_info t1 
	LEFT JOIN exam_record t2 ON t1.uid = t2.uid
	WHERE `level` = 6 OR `level` = 7
	
	UNION ALL
	
	SELECT t1.uid,
		DATE_FORMAT(submit_time,'%Y%m') act_month,
		DATE(submit_time) act_date,
		'question' tag
	FROM user_info t1 
	LEFT JOIN practice_record t2 ON t1.uid = t2.uid
	WHERE `level` = 6 OR `level` = 7
) t
GROUP BY uid
ORDER BY act_month_total DESC,act_days_2021 DESC;