题解 | 大小写混乱时的筛选统计
大小写混乱时的筛选统计
http://www.nowcoder.com/practice/81cb12931a604811ae69d332515c7734
select用了个关联子查询(感觉好久没用了)
with t as (
select tag,sum(count(*)) over w as answer_cnt,count(*) cnt
from exam_record join examination_info using(exam_id)
group by tag
window w as (partition by upper(tag))
)
select t1.tag
,(select cnt from t as t2 where upper(t1.tag)=t2.tag) as answer_cnt
from t as t1
where answer_cnt-cnt>0 and cnt<3 and tag!=upper(tag)