文章来源于网络收集而来，版权归原创者所有，如有侵权请及时联系！

连接查询

发布于 2024-08-17 15:49:00 字数 14680 浏览 0 评论 0 收藏 0

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

描述：

现有用户信息表 user_info（uid 用户 ID，nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间）：

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客 1 号	3100	7	算法	2020-01-01 10:00:00
2	1002	牛客 2 号	2300	7	算法	2020-01-01 10:00:00
3	1003	牛客 3 号	2500	7	算法	2020-01-01 10:00:00
4	1004	牛客 4 号	1200	5	算法	2020-01-01 10:00:00
5	1005	牛客 5 号	1600	6	C++	2020-01-01 10:00:00
6	1006	牛客 6 号	2000	6	C++	2020-01-01 10:00:00

试卷信息表 examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）：

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2021-09-01 06:00:00
2	9002	C++	hard	60	2021-09-01 06:00:00
3	9003	算法	medium	80	2021-09-01 10:00:00

试卷作答记录表 exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2021-09-01 09:01:01	2021-09-01 09:31:00	81
2	1002	9002	2021-09-01 12:01:01	2021-09-01 12:31:01	81
3	1003	9001	2021-09-01 19:01:01	2021-09-01 19:40:01	86
4	1003	9002	2021-09-01 12:01:01	2021-09-01 12:31:51	89
5	1004	9001	2021-09-01 19:01:01	2021-09-01 19:30:01	85
6	1005	9002	2021-09-01 12:01:01	2021-09-01 12:31:02	85
7	1006	9003	2021-09-07 10:01:01	2021-09-07 10:21:01	84
8	1006	9001	2021-09-07 10:01:01	2021-09-07 10:21:01	80

题目练习记录表 practice_record（uid 用户 ID, question_id 题目 ID, submit_time 提交时间, score 得分）：

id	uid	question_id	submit_time	score
1	1001	8001	2021-08-02 11:41:01	60
2	1002	8001	2021-09-02 19:30:01	50
3	1002	8001	2021-09-02 19:20:01	70
4	1002	8002	2021-09-02 19:38:01	70
5	1004	8001	2021-08-02 19:38:01	70
6	1004	8002	2021-08-02 19:48:01	90
7	1001	8002	2021-08-02 19:38:01	70
8	1004	8002	2021-08-02 19:48:01	90
9	1004	8002	2021-08-02 19:58:01	94
10	1004	8003	2021-08-02 19:38:01	70
11	1004	8003	2021-08-02 19:48:01	90
12	1004	8003	2021-08-01 19:38:01	80

请你找到高难度 SQL 试卷得分平均值大于 80 并且是 7 级的红名大佬，统计他们的 2021 年试卷总完成次数和题目总练习次数，只保留 2021 年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

示例数据输出如下：

uid	exam_cnt	question_cnt
1001	1	2
1003	2	0

解释：用户 1001、1003、1004、1006 满足高难度 SQL 试卷得分平均值大于 80，但只有 1001、1003 是 7 级红名大佬；1001 完成了 1 次试卷 1001，练习了 2 次题目；1003 完成了 2 次试卷 9001、9002，未练习题目（因此计数为 0）

思路：

先将条件进行初步筛选，比如先查出做过高难度 sql 试卷的用户

SELECT
    record.uid
FROM
    exam_record record
    INNER JOIN examination_info e_info ON record.exam_id = e_info.exam_id
    JOIN user_info u_info ON record.uid = u_info.uid
WHERE
    e_info.tag = 'SQL'
    AND e_info.difficulty = 'hard'

然后根据题目要求，接着再往里叠条件即可；

但是这里又要注意：

第一：不能 YEAR(submit_time)= 2021 这个条件放到最后，要在 ON 条件里，因为左连接存在返回左表全部行，右表为 null 的情形，放在 JOIN 条件的 ON 子句中的目的是为了确保在连接两个表时，只有满足年份条件的记录会进行连接。这样可以避免其他年份的记录被包含在结果中。即 1001 做过 2021 年的试卷，但没有练习过，如果把条件放到最后，就会排除掉这种情况。

第二，必须是 COUNT(distinct er.exam_id) exam_cnt, COUNT(distinct pr.id) question_cnt， 要加 distinct，因为有左连接产生很多重复值。

答案：

SELECT er.uid AS UID,
       count(DISTINCT er.exam_id) AS exam_cnt,
       count(DISTINCT pr.id) AS question_cnt
FROM exam_record er
LEFT JOIN practice_record pr ON er.uid = pr.uid
AND YEAR (er.submit_time)= 2021
AND YEAR (pr.submit_time)= 2021
WHERE er.uid IN
    (SELECT er.uid
     FROM exam_record er
     LEFT JOIN examination_info ei ON er.exam_id = ei.exam_id
     LEFT JOIN user_info ui ON er.uid = ui.uid
     WHERE tag = 'SQL'
       AND difficulty = 'hard'
       AND LEVEL = 7
     GROUP BY er.uid
     HAVING avg(score) > 80)
GROUP BY er.uid
ORDER BY exam_cnt,
         question_cnt DESC

可能细心的小伙伴会发现，为什么明明将条件限制了 tag = 'SQL' AND difficulty = 'hard' ，但是用户 1003 仍然能查出两条考试记录，其中一条的考试 tag 为 C++ ; 这是由于 LEFT JOIN 的特性，即使没有与右表匹配的行，左表的所有记录仍然会被保留。

每个 6/7 级用户活跃情况（困难）

描述：

现有用户信息表 user_info （ uid 用户 ID， nick_name 昵称, achievement 成就值, level 等级, job 职业方向, register_time 注册时间）：

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客 1 号	3100	7	算法	2020-01-01 10:00:00
2	1002	牛客 2 号	2300	7	算法	2020-01-01 10:00:00
3	1003	牛客 3 号	2500	7	算法	2020-01-01 10:00:00
4	1004	牛客 4 号	1200	5	算法	2020-01-01 10:00:00
5	1005	牛客 5 号	1600	6	C++	2020-01-01 10:00:00
6	1006	牛客 6 号	2600	7	C++	2020-01-01 10:00:00

试卷信息表 examination_info （ exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）：

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2021-09-01 06:00:00
2	9002	C++	easy	60	2021-09-01 06:00:00
3	9003	算法	medium	80	2021-09-01 10:00:00

试卷作答记录表 exam_record （ uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

uid	exam_id	start_time	submit_time	score
1001	9001	2021-09-01 09:01:01	2021-09-01 09:31:00	78
1001	9001	2021-09-01 09:01:01	2021-09-01 09:31:00	81
1005	9001	2021-09-01 19:01:01	2021-09-01 19:30:01	85
1005	9002	2021-09-01 12:01:01	2021-09-01 12:31:02	85
1006	9003	2021-09-07 10:01:01	2021-09-07 10:21:59	84
1006	9001	2021-09-07 10:01:01	2021-09-07 10:21:01	81
1002	9001	2020-09-01 13:01:01	2020-09-01 13:41:01	81
1005	9001	2021-09-01 14:01:01	(NULL)	(NULL)

题目练习记录表 practice_record （ uid 用户 ID, question_id 题目 ID, submit_time 提交时间, score 得分）：

uid	question_id	submit_time	score
1001	8001	2021-08-02 11:41:01	60
1004	8001	2021-08-02 19:38:01	70
1004	8002	2021-08-02 19:48:01	90
1001	8002	2021-08-02 19:38:01	70
1004	8002	2021-08-02 19:48:01	90
1006	8002	2021-08-04 19:58:01	94
1006	8003	2021-08-03 19:38:01	70
1006	8003	2021-08-02 19:48:01	90
1006	8003	2020-08-01 19:38:01	80

请统计每个 6/7 级用户总活跃月份数、2021 年活跃天数、2021 年试卷作答活跃天数、2021 年答题活跃天数，按照总活跃月份数、2021 年活跃天数降序排序。由示例数据结果输出如下：

uid	act_month_total	act_days_2021	act_days_2021_exam
1006	3	4	1
1001	2	2	1
1005	1	1	1
1002	1	0	0
1003	0	0	0

解释：6/7 级用户共有 5 个，其中 1006 在 202109、202108、202008 共 3 个月活跃过，2021 年活跃的日期有 20210907、20210804、20210803、20210802 共 4 天，2021 年在试卷作答区 20210907 活跃 1 天，在题目练习区活跃了 3 天。

思路：

这题的关键在于 CASE WHEN THEN 的使用，不然要写很多的 left join 因为会产生很多的结果集。

CASE WHEN THEN 语句是一种条件表达式，用于在 SQL 中根据条件执行不同的操作或返回不同的结果。

语法结构如下：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END

在这个结构中，可以根据需要添加多个 WHEN 子句，每个 WHEN 子句后面跟着一个条件（condition）和一个结果（result）。条件可以是任何逻辑表达式，如果满足条件，将返回对应的结果。

最后的 ELSE 子句是可选的，用于指定当所有前面的条件都不满足时的默认返回结果。如果没有提供 ELSE 子句，则默认返回 NULL 。

例如：

SELECT score,
    CASE
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade
FROM student_scores;

在上述示例中，根据学生成绩（score）的不同范围，使用 CASE WHEN THEN 语句返回相应的等级（grade）。如果成绩大于等于 90，则返回"优秀"；如果成绩大于等于 80，则返回"良好"；如果成绩大于等于 60，则返回"及格"；否则返回"不及格"。

那了解到了上述的用法之后，回过头看看该题，要求列出不同的活跃天数。

count(distinct act_month) as act_month_total,
count(distinct case when year(act_time)='2021'then act_day end) as act_days_2021,
count(distinct case when year(act_time)='2021' and tag='exam' then act_day end) as act_days_2021_exam,
count(distinct case when year(act_time)='2021' and tag='question'then act_day end) as act_days_2021_question

这里的 tag 是先给标记，方便对查询进行区分，将考试和答题分开。

找出试卷作答区的用户

SELECT
        uid,
        exam_id AS ans_id,
        start_time AS act_time,
        date_format( start_time, '%Y%m' ) AS act_month,
        date_format( start_time, '%Y%m%d' ) AS act_day,
        'exam' AS tag
    FROM
        exam_record

紧接着就是答题作答区的用户

SELECT
        uid,
        question_id AS ans_id,
        submit_time AS act_time,
        date_format( submit_time, '%Y%m' ) AS act_month,
        date_format( submit_time, '%Y%m%d' ) AS act_day,
        'question' AS tag
    FROM
        practice_record

最后将两个结果进行 UNION 最后别忘了将结果进行排序（这题有点类似于分治法的思想）

答案：

SELECT user_info.uid,
       count(DISTINCT act_month) AS act_month_total,
       count(DISTINCT CASE
                          WHEN YEAR (act_time)= '2021' THEN act_day
                      END) AS act_days_2021,
       count(DISTINCT CASE
                          WHEN YEAR (act_time)= '2021'
                               AND tag = 'exam' THEN act_day
                      END) AS act_days_2021_exam,
       count(DISTINCT CASE
                          WHEN YEAR (act_time)= '2021'
                               AND tag = 'question' THEN act_day
                      END) AS act_days_2021_question
FROM
  (SELECT UID,
          exam_id AS ans_id,
          start_time AS act_time,
          date_format(start_time, '%Y%m') AS act_month,
          date_format(start_time, '%Y%m%d') AS act_day,
          'exam' AS tag
   FROM exam_record
   UNION ALL SELECT UID,
                    question_id AS ans_id,
                    submit_time AS act_time,
                    date_format(submit_time, '%Y%m') AS act_month,
                    date_format(submit_time, '%Y%m%d') AS act_day,
                    'question' AS tag
   FROM practice_record) total
RIGHT JOIN user_info ON total.uid = user_info.uid
WHERE user_info.LEVEL IN (6,
                          7)
GROUP BY user_info.uid
ORDER BY act_month_total DESC,
         act_days_2021 DESC

较难或者困难的题目可以根据自身实际情况和面试需要来决定是否要跳过。

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

列表为空，暂无数据

连接查询

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

每个 6/7 级用户活跃情况（困难）

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。