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

SELECT
    uid
    ,nick_name
    ,achievement
FROM user_info
LEFT JOIN (
    SELECT uid
            ,MAX(DATE_FORMAT(submit_time, "%Y%m")) AS date_ex
    FROM user_info
    JOIN exam_record USING(uid)
    GROUP BY uid
) data1 USING(uid)
LEFT JOIN (
    SELECT uid
            ,MAX(DATE_FORMAT(submit_time, "%Y%m")) AS date_pr
    FROM user_info
    JOIN practice_record USING(uid)
    GROUP BY uid
) data2 USING(uid)
WHERE nick_name LIKE "牛客%号" 
        AND achievement BETWEEN 1200 AND 2500 
        AND (date_ex = "202109" OR date_pr = "202109" )

• 一定要使用LEFT JOIN，否则会因为两次JOIN连接删除某些用户，违背后期OR的需要，从而导致结果中只使用JOIN会报错