题解 | #筛选限定昵称成就值活跃日期的用户#

# 前两个条件可以从用户表得到，谓词、成就值
# 最后一个要筛选用户uid，新增一列是用户最近的一次活跃时间

# 临时表，左连接三个表，新增每个用户的试卷最新记录时间和练习题最新记录时间
WITH temp_0 AS(
    SELECT a.uid uid, 
        #DATE_FORMAT(b.start_time, '%Y%m') exam_t,
        #DATE_FORMAT(c.submit_time, '%Y%m') ques_t,
        MAX(b.start_time) OVER(PARTITION BY uid) max_exam_t,
        MAX(c.submit_time) OVER(PARTITION BY uid) max_ques_t
    FROM user_info a
        LEFT JOIN exam_record b USING(uid)
        LEFT JOIN practice_record c USING(uid)
)

# 主查询，限制用户名和成就值条件，还有最新活跃记录符合要求的uid
SELECT uid, nick_name, achievement
FROM user_info 
WHERE nick_name LIKE '牛客%号' AND achievement BETWEEN 1200 AND 2500 AND uid IN (
    SELECT DISTINCT uid
    FROM temp_0
    WHERE DATE_FORMAT(max_exam_t,'%Y%m') = 202109 OR DATE_FORMAT(max_ques_t,'%Y%m') = 202109
    )

踩的坑：1.临时表的联结最先弄成自连接的

2. 最后筛选活跃记录最先弄成了选择最大日期

思路：先计算得到每个用户在试卷和练习题上的最新活跃日期，再筛选符合要求的uid；最后谓词限定用户名，between限制成就值

知识点：% 被用作通配符，表示任意数量（包括零个）的任意字符。它通常用于模糊匹配

_（下划线）是用来匹配任意单个字符的通配符

? 用作一个参数的占位符，它可以代表任何值，无论长度如何。这些参数在查询执行之前会被具体的值所替换。

踩的坑的解析：

1. 内连接 vs 外连接：外连接 (LEFT JOIN) 会保留 user_info 表中的所有记录，即使在 exam_record 或 practice_record 表中没有对应的记录。内连接 (INNER JOIN) 只会保留那些在 user_info, exam_record 和 practice_record 表中都有匹配记录的行。如果某个用户在 exam_record 或 practice_record 表中没有记录，那么这个用户的信息将不会出现在结果中。
2. 结果集的差异：使用内连接，您将仅获取那些同时在 user_info, exam_record 和 practice_record 表中都有记录的用户。这可能导致您丢失那些仅在 user_info 表中有记录，但在另外两个表中没有记录的用户数据。
3. 查询逻辑的一致性：确保您的业务逻辑需要这样的数据筛选。如果您的目的是要找出那些参加过考试且有练习记录的用户，那么内连接是合适的。如果您还想包含那些可能只在 user_info 表中有记录，但在 exam_record 或 practice_record 表中没有记录的用户，那么您应该使用外连接。

您提供的 SQL 代码片段中包含了 DATE_FORMAT 函数和 IF 函数的组合。这个组合是用来确定 max_exam_t 和 max_ques_t 两个日期中较晚的一个，然后将其格式化为 '%Y%m' 格式，并检查这个日期是否与 '202109' 相等。

原始的代码片段是这样的：

WHERE DATE_FORMAT(max_exam_t, '%Y%m') = '202109' 
      OR DATE_FORMAT(max_ques_t, '%Y%m') = '202109'

它检查 max_exam_t 或 max_ques_t 任一的月份是否为 '202109'。

修改后的代码片段：

DATE_FORMAT(IF(max_exam_t >= max_ques_t, max_exam_t, max_ques_t), '%Y%m') = '202109'

这个修改后的条件将比较 max_exam_t 和 max_ques_t 的值，取最大的一个（即最晚的日期），然后格式化并检查是否为 '202109'。

这个改变的意义：

1. 原始条件检查两个日期中任何一个是否符合特定的月份 '202109'。
2. 修改后的条件仅检查两个日期中最晚的那个是否符合特定的月份 '202109'。

因此，原始条件可能会选择更多的记录，因为它允许任一日期符合条件即可；而修改后的条件更加严格，只有当两个日期中的较晚者符合条件时，记录才会被选中。这个逻辑上的差异会导致结果集中可能包含的记录数量不同。