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

with a as
#   先计算所有带大小写字母的tag的作答数，存临时表a
(select  tag,count(start_time) answer_cnt
from exam_record er
join examination_info ei on ei.exam_id=er.exam_id
where tag REGEXP '[a-zA-Z]'
group by tag),
# 计算所有带大小写字母的tag的作答数小于3的数，存临时表b
b as(
select tag,count(start_time) answer_cnt
from exam_record er
join examination_info ei on ei.exam_id=er.exam_id
where tag REGEXP '[a-zA-Z]'
group by tag
having answer_cnt<3)
#  2张表自连接，取小写字母做tag，然后取大写字母对应的作答数，
select b.tag tag,a.answer_cnt answer_cnt
 from a,b where a.tag<>b.tag and upper(b.tag)=a.tag