为什么这样写活动一的输出结果中会有uid=none而没有1005的记录呀~~已经限制了非空了呀,又是想了一下午啥没想出来的一天。
select * from(
select if(count(if(score>=85,1,null))=count(uid),uid,null) as uid,'activity1' as activity
from
(select uid,score,submit_time,start_time,release_time from exam_record
join examination_info using(exam_id)
where tag='SQL'
)a
where uid is not null
group by uid
)t1
union
select * from(
select if(count(if(timestampdiff(second,start_time,submit_time)<=duration*30 and score>80 and year(start_time)=2021 and difficulty='hard',1,null))>=1,uid,null) as uid,'activity2' as activity
from
(select uid,score,submit_time,start_time,release_time,duration,difficulty from exam_record
join examination_info using(exam_id)
where tag='SQL'
)a
where uid is not null
group by uid
) t2
where uid is not null
order by uid