with users as(SELECT er.uid from exam_record er join examination_info ei on er.exam_id=ei.exam_id join user_info ui on er.uid=ui.uid WHERE ei.tag='SQL' and ei.difficulty='hard' and ui.level=7 GROUP by er.uid having avg(er.score) >80)

select te.uid,(ifnull(te.ers,0)+ifnull(tp.ers,0)) as exam_cnt,(ifnull(te.prs,0)+ifnull(tp.prs,0)) as question_cnt from users as u left join (select upe.uid,count(er.score) as ers ,0 as prs from exam_record er join users as upe on er.uid=upe.uid where YEAR(er.submit_time)='2021' group by upe.uid) as te on u.uid=te.uid left join (select upe.uid,0 as ers,count(pr.score) as prs from practice_record pr join users as upe on pr.uid=upe.uid where YEAR(pr.submit_time)='2021' group by upe.uid ) as tp on u.uid= tp.uid ORDER BY exam_cnt,question_cnt DESC

我表示写吐了 写了一篇作文 纠结的点在于咋个将两个表的字段通过列联合，自己就先分别统计 两张表的数量 然后造两个字段进行join然后输出的时候 加起来 其中比较重要的一点 一定要把 求出来的users表作为根基然后两张表进行left jion 刚开始用 类似full join的方式连接考试练习两张表发现并不得行，所以得用自己的users表 新学用法 with as 不过我在 navicat里使用就会报错 不知道什么原因 有大脑愿意跟我说一下吗 万分感谢