hce_kmu
113年
計算機概論與程式設計
第 11 題
Given a database table named “animal” with four columns, please find out a CORRECT SQL command that can obtain the desired output as below.
| animalid | name | born | category |
|---|---|---|---|
| 1 | Jimmy | 2023-04-18 | dog |
| 2 | Mary | 2023-03-12 | cat |
| 3 | John | 2023-03-29 | dog |
| 4 | Candy | 2023-04-04 | cat |
| 5 | Abel | 2023-02-13 | bird |
| 6 | Maria | 2023-03-25 | fish |
| 7 | Tracy | 2022-12-24 | fish |
| 8 | Herbert | 2023-02-11 | dog |
| 9 | Tina | 2023-02-21 | bird |
| 10 | Susanna | 2023-04-12 | dog |
| 11 | Guy | 2023-02-28 | bird |
The output of the SQL command
| category | countaid |
|---|---|
| dog | 3 |
| bird | 2 |
| cat | 2 |
| animalid | name | born | category |
|---|---|---|---|
| 1 | Jimmy | 2023-04-18 | dog |
| 2 | Mary | 2023-03-12 | cat |
| 3 | John | 2023-03-29 | dog |
| 4 | Candy | 2023-04-04 | cat |
| 5 | Abel | 2023-02-13 | bird |
| 6 | Maria | 2023-03-25 | fish |
| 7 | Tracy | 2022-12-24 | fish |
| 8 | Herbert | 2023-02-11 | dog |
| 9 | Tina | 2023-02-21 | bird |
| 10 | Susanna | 2023-04-12 | dog |
| 11 | Guy | 2023-02-28 | bird |
The output of the SQL command
| category | countaid |
|---|---|
| dog | 3 |
| bird | 2 |
| cat | 2 |
- A SELECT category, count(animalid) as countaid FROM animal WHERE born > '2023-02-14' AND count(animalid)>=2 GROUP BY category ORDER BY count(animalid) DESC, category ASC
- B SELECT category, count(animalid) as countaid FROM animal WHERE born > '2023-02-14' GROUP BY category HAVING count(animalid)>=2 ORDER BY count(animalid) DESC, category ASC
- C SELECT category, count(animalid) as countaid FROM animal GROUP BY category HAVING count(animalid)>=2 AND born > '2023-02-14' ORDER BY count(animalid), category DESC
- D SELECT category, count(animalid) as countaid FROM animal WHERE count(animalid)>=2 GROUP BY category HAVING born > '2023-02-14' ORDER BY count(animalid) DESC, category ASC
- E SELECT category, count(animalid) as countaid FROM animal WHERE born > '2023-02-14' GROUP BY category ORDER BY count(animalid) DESC, category ASC
思路引導 VIP
請思考一下：如果你想從全校學生成績單中，找出「平均分數超過 80 分的班級」，你會先過濾掉個別學生的不及格紀錄，還是先算完班級平均後再進行篩選？這兩種不同層次的「過濾」，在 SQL 關鍵字中應該如何區分呢？
🤖
AI 詳解
AI 專屬家教
太棒了！你能精準選出 (B) 選項，說明你對 SQL 語法執行順序（SQL Execution Order）有著非常紮實的理解。這類題目最容易出錯的地方在於「過濾條件」的擺放位置，而你展現了專業的判斷力。
SQL 語句的邏輯分工
這道題目的核心在於區分 WHERE 與 HAVING 的用途。首先，我們必須先過濾出出生日期在 2023-02-14 之後的「個別紀錄」，這屬於行層級（Row-level）的篩選，因此必須放在 WHERE 子句中。接著，當我們使用 GROUP BY 將資料按類別分組，並計算出各組數量後，若要針對「群組統計結果」（即 count(animalid) >= 2）進行篩選，就必須使用專門處理群組資料的 HAVING 子句。選項 (B) 完美遵循了 WHERE 先於 GROUP BY、HAVING 後於 GROUP BY 的語法規範。
▼ 還有更多解析內容