题解 | #分别满足两个活动的人#

SELECT DISTINCT uid, 'activity1' AS sctivity
FROM exam_record
WHERE score >= 85 AND YEAR(submit_time)=2021
UNION
SELECT DISTINCT uid, 'activity2' AS sctivity
FROM (
    SELECT
        uid, start_time, submit_time, score, 
        TIMESTAMPDIFF(MINUTE,start_time,submit_time) AS spend_time,
        IF(TIMESTAMPDIFF(MINUTE,start_time,submit_time) < (duration/2),1,0) AS early_finish,
        difficulty
    FROM exam_record
    JOIN examination_info USING(exam_id)
) AS er_ei
WHERE difficulty='hard' AND score > 80 AND YEAR(submit_time)=2021
GROUP BY uid
HAVING SUM(early_finish) >= 1
ORDER BY uid;