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

以下题解均未通过，人麻了,感觉思路没问题，测试结果一样,提交出错,搞不懂

思路①筛选 符合 ‘高难度SQL试卷得分平均值大于80并且是7级’ 条件的 uid 见表m

②left join 连接exam_record a和practice_record a1，因为exam_record表uid种类多，放前面不会丢失uid

筛选条件a.uid in (select uid from m) and year(a.submit_time)=2021

分组a.uid 排序 order by exam_cnt asc,question_cnt desc

完整代码

with m as (

select uid

from (select t.uid as uid,score

from exam_record t left join examination_info t1 on t.exam_id=t1.exam_id

left join user_info t2 on t.uid=t2.uid

where tag='SQL' and difficulty='hard' and level =7

) t3

group by uid

having avg(score) >80

)

select a.uid as uid,

any_value(count(distinct case when year(a.submit_time)=2021 then a.

submit_time end )) as exam_cnt,

any_value(count(case when year(a1.submit_time)=2021 then a1.

submit_time end )) as question_cnt

from exam_record a left join practice_record a1 on a.uid=a1.uid

{select* 结果 }

where a.uid in (select uid from m) and year(a.submit_time)=2021

group by a.uid

order by exam_cnt asc,question_cnt desc;

重新写，提交也通不过

思路 把m也加入连接表

select * from from m left join exam_record t on m.uid=t.uid and year(t.start_time)=2021

left join practice_record t1 on m.uid=t1.uid and year(t1.submit_time)=2021

测试结果

with m as (

select uid

from (select t.uid as uid,score

from exam_record t left join examination_info t1 on t.exam_id=t1.exam_id

left join user_info t2 on t.uid=t2.uid

where tag='SQL' and difficulty='hard' and level =7

) t3

group by uid

having avg(score) >80

)

select m.uid,

count(distinct t.submit_time) as exam_cnt,

count(t1.submit_time) as question_cnt

from m left join exam_record t on m.uid=t.uid and year(t.start_time)=2021

left join practice_record t1 on m.uid=t1.uid and year(t1.submit_time)=2021

group by m.uid

order by exam_cnt asc,question_cnt desc