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

select a.uid, 
	count(distinct 
      	case when year(b.submit_time) = '2021' then b.id 
      	else null end) as exam_cnt, 
	count(distinct 
     	case when year(c.submit_time) = '2021' then c.id
     	else null end) as question_cnt
from (select a.uid
  	from user_info as a 
  	inner join exam_record as b using(uid)
  	inner join examination_info as ei using(exam_id)
  	where a.level = 7 and ei.tag = 'SQL' and ei.difficulty = 'hard' 
  	and b.submit_time is not null
  	group by a.uid 
  	having avg(b.score) >80
 	) as a 
left join exam_record as b on a.uid = b.uid
left join practice_record as c on a.uid = c.uid
group by a.uid
order by exam_cnt, question_cnt desc

第三行count用于b.id是因为这几个表的主键都是ID，用sum if函数应该也可以 大概思路就是先把高难度、SQL、7级、均分大于80 这几个满足条件的用户id筛选出来，注意第16行临时表一定要有别名。然后再增加其他筛选条件。