SQL155 大小写混乱时的筛选统计
题目理解
假设我们有一个考试系统数据库，里面有：
exam_record表：记录学生考试作答情况examination_info表：记录考试信息，包括每个考试的类别标签(tag)
问题描述：
有些考试的分类标签(tag)可能存在大小写混乱的情况，比如"Math"和"MATH"其实是同一个分类，但被记录成了不同形式。
题目要求：
- 先找出那些作答次数很少(少于3次)的标签
- 然后看看这些标签如果统一转成大写后，对应的大写标签原本有多少人作答
- 如果标签本身就是大写的(转换前后没变化)，就不显示
- 最后结果显示小写的标签和对应的大写标签的原始作答数
举例说明
假设数据如下：
| tag | 作答次数 |
|---|---|
| Math | 2 |
| MATH | 10 |
| java | 1 |
| JAVA | 15 |
| PYTHON | 20 |
按照题目要求：
- 找出作答数小于3的标签：Math(2), java(1)
- 将它们转大写后对应的原始作答数：
- "MATH"对应10
- "JAVA"对应15
- 排除"PYTHON"因为它本身就是大写(转换前后没变化)
- 最终显示：
小写tag 大写tag原始作答数 math 10 java 15
SQL解析
-- 第一步：计算每个标签的总作答次数
WITH t_tag_count AS (SELECT tag, COUNT(uid) as answer_cntFROM exam_recordJOIN examination_info USING(exam_id)GROUP BY tag
)-- 第二步：找出需要的结果
SELECT LOWER(a.tag) as tag, -- 显示小写的标签b.answer_cnt -- 显示对应大写标签的原始作答数
FROM t_tag_count as a -- 作答数少的标签
JOIN t_tag_count as b -- 对应的大写标签
ON UPPER(a.tag) = b.tag -- a标签转大写后等于b标签
AND a.tag != b.tag -- 排除大小写相同的情况
AND a.answer_cnt < 3 -- 只找作答数小于3的标签为什么这样写？
- 先统计每个标签的作答总数
- 然后自连接：
- 连接条件1：a标签转大写等于b标签（找到大小写不同的相同标签）
- 连接条件2：a和b不是同一个标签（排除大小写相同的情况）
- 连接条件3：a的作答数小于3（题目要求）
- 最后显示小写的标签和对应大写标签的原始作答数
