题解 | #满足条件的用户的试卷完成数和题目练习数#

1、注意distinct内容

count(distinct e.exam_id,e.submit_time) as exam_cnt
, count(distinct p.question_id,p.submit_time) as question_cnt
有一个为null就不算

2、临时表

with 表名 as

（）

不能单独运行

创建虚拟表，制作

with users as
(
        select a.uid,AVG(score) score1
        from exam_record a
        left join user_info b
        on a.uid=b.uid
        left join examination_info c
        on a.exam_id=c.exam_id
        where b.level=7 and  c.tag='SQL' and c.difficulty='hard'
        group by a.uid
        having AVG(Score)>80
)

select c.uid,exam_cnt,IFNULL(question_cnt1,0) question_cnt
from 
            (
            select uid,count(distinct exam_id,submit_time) exam_cnt
            from exam_record 
            where Year(start_time)=2021 
            group by uid
            ) c
left join users e
on c.uid=e.uid 
left join
(
        select uid,count(distinct question_id,submit_time) question_cnt1
        from practice_record 
        where Year(submit_time)=2021
        group by uid
) d
on c.uid=d.uid
 where e.uid is not null
order by exam_cnt asc,question_cnt desc

方法二：

自查讯/join

select c.uid,exam_cnt,IFNULL(question_cnt1,0) question_cnt
from 
            (
            select uid,count(distinct exam_id,submit_time) exam_cnt
            from exam_record 
            where Year(start_time)=2021 and uid
            in
                (
                        select a.uid
                    from exam_record a
                    left join user_info b
                    on a.uid=b.uid
                    left join examination_info c
                    on a.exam_id=c.exam_id
                    where b.level=7 and  c.tag='SQL' and c.difficulty='hard'
                    group by a.uid
                    having AVG(Score)>80
                )

            group by uid
) c
left join
(
        select uid,count(distinct question_id,submit_time) question_cnt1
        from practice_record 
        where Year(submit_time)=2021
        group by uid
) d
on c.uid=d.uid
order by exam_cnt asc,question_cnt desc