题解 | #大小写混乱时的筛选统计#

1. 统计出每套试卷作答数小于3的试卷类型和试卷作答数量； tag 小写转大写后需要保持一致，两张表的tag值本身需要不相等。


select A.tag,
       B.answer_cntB answer_cnt  
  from (
  select EI.tag ,
         count(ER.start_time) answer_cntA
    from  exam_record ER join examination_info EI using(exam_id) 
   group by ER.exam_id 
   having count(ER.start_time) < 3 
   ) A  join (
     select EI.tag,
            count(ER.start_time) answer_cntB 
       from exam_record ER join examination_info EI using(exam_id) 
       group by EI.tag 
   ) B 
   where  A.tag != B.tag 
     and upper(A.tag) = B.tag ;