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

with table1 as (
select tag,count(er.exam_id) answer_cnt 
from exam_record er left join examination_info ei on er.exam_id=ei.exam_id
group by tag) 
select a.tag,b.answer_cnt from 
table1 as a join table1 as b
on upper(a.tag)=b.tag and a.tag!=b.tag and a.answer_cnt<3
;

看的评论区的写的，就记录下笔记让自己明白下为什么是这样~

新建表1，统计不同tag的作答次数：

with table1 as (

select tag,count(er.exam_id) answer_cnt

from exam_record er left join examination_info ei on er.exam_id=ei.exam_id

group by tag)

表1：

将表1作为表a，表b进行自连接：

select a.tag,b.answer_cnt from

table1 as a join table1 as b

on upper(a.tag)=b.tag and a.tag!=b.tag and a.answer_cnt<3

条件1：表a的tag大写后与表b 的tag相同：

upper(a.tag)=b.tag

条件2：表a的tag不等于表b 的tag：

a.tag!=b.tag

条件3：表a中对应tag的作答次数小于3

a.answer_cnt<3