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

#筛选出试卷作答数小于3的类别tag
with a as(
select tag,
count(er.exam_id) as cnt

from examination_info ei
join exam_record er on ei.exam_id = er.exam_id
group by tag
),
b as(
    select tag,
count(er.exam_id) as cnt

from examination_info ei
join exam_record er on ei.exam_id = er.exam_id
group by tag
having cnt <3
)

select lower(upper_tag) as tag,cnt as answer_cnt from(
select upper(b.tag) as upper_tag,a.cnt
from b
join a on upper(b.tag) = a.tag
and a.cnt!=b.cnt)c

思路就是

1. 先求每个tag的做过的试卷个数，a表未加筛选having cnt <3，b表增加筛选having cnt <3
2. 从b中取tag，并对此tag进行upper操作，再用upper后的tag匹配a表中的字段，且两表的cnt要不等，这样就可以取出C++的cnt为6
3. 最后一步把大写的C++变为小写