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（題目要求）
- 最后顯示小寫的標簽和對應大寫標簽的原始作答數
