通过代码
SELECT
distinct uid uid,
'activity1' activity
FROM
exam_record
WHERE
uid not in (SELECT
uid
FROM
exam_record
WHERE
score<85
and YEAR(submit_time) = 2021
)
UNION
SELECT
distinct uid uid,
'activity2' activity
FROM
exam_record e_r
left JOIN
examination_info e_i
on e_r.exam_id = e_i.exam_id
WHERE
YEAR(submit_time) = 2021
and difficulty = 'hard'
and TIMESTAMPDIFF(SECOND,start_time,submit_time) <= duration *30
and score>80
ORDER BY
uid
条件有点多查着有点麻烦别的也没啥,查activity1也可以用having来写
select
uid,
'activity1' activity
from
exam_record
#where
#year(submit_time) = 2021
group by
uid
having
min(score) >= 85
之所以把where year注释掉,是因为我发现查不查year都能通过,不查还快一点。/doge