题解 | #大小写混乱时的筛选统计#
大小写混乱时的筛选统计
https://www.nowcoder.com/practice/81cb12931a604811ae69d332515c7734
# with t1 as (select tag,count(*) over(partition by tag) as answer_cnt from examination_info join exam_record using(exam_id))
# select distinct a.tag,b.answer_cnt from t1 as a join t1 as b on upper(a.tag)=b.tag and a.tag!=b.tag where a.answer_cnt<3 order by answer_cnt desc,tag desc
#别人写的
WITH t_tag_count as (
SELECT tag, COUNT(uid) as answer_cnt
FROM exam_record
LEFT JOIN examination_info USING(exam_id)
GROUP BY tag
)
SELECT a.tag, b.answer_cnt
FROM t_tag_count as a
JOIN t_tag_count as b
ON UPPER(a.tag) = b.tag and a.tag != b.tag and a.answer_cnt < 3;
投递莉莉丝游戏等公司10个岗位 >