MySQL 的 WHERE, HAVING, GROUP BY, AS 的使用規則

如果我們幫資料欄位命名一個別名(AS new_name),要注意這個別名不是所有 SQL 語法都可以讀的到。
要先搞懂在一串很長的 SQL 語法,是如何解析該語法,先解析那一個部份?什麼可以解析,什麼不可以解析?
“SQL is evaluated backwards, from right to left. So the where clause is parsed and evaluate prior to the select clause. Because of this the aliasing of u_name to user_name has not yet occurred.”
“A select_expr can be given an alias using AS alias_name. The alias is used as the expression’s column name and can be used in GROUP BY, ORDER BY, or HAVING clauses.”

WHERE與HAVING有何差異呢?
(1) 沒有GROUP BY的時候,只使用WHERE而不使用HAVING。
(2) 有GROUP BY的時候,WHERE在GROUP BY前面,HAVING在GROUP BY後面。
(3) 使用HAVING的時候,只用在跟GROUP BY相關函數有關的條件上。

假設我有一張表,欄位為 id, name ,…

mysql> describe bowler;
+---------+--------------------------+------+-----+---------+----------------+
| Field   | Type                     | Null | Key | Default | Extra          |
+---------+--------------------------+------+-----+---------+----------------+
| id      | smallint(5) unsigned     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255)             | NO   |     | NULL    |                |
| pic     | varchar(255)             | YES  |     | NULL    |                |
| account | varchar(255)             | NO   |     | NULL    |                |
| passwd  | varchar(255)             | NO   |     | NULL    |                |
| phone   | varchar(255)             | YES  |     | NULL    |                |
| status  | enum('0','1','2','3')    | NO   |     | 1       |                |
| class   | enum('member','captain') | NO   |     | member  |                |
| team_id | int(10) unsigned         | NO   |     | 0       |                |
+---------+--------------------------+------+-----+---------+----------------+

簡單測試一下 WHERE 的條件可不可使用別名

select id as new_id,name as new_name from bowler where new_id = 1;

不行,會返回錯誤訊息 Unknown Column In Where Clause

以下是結合 3 張表,用來查詢球隊裡隊員 2015 年度的平均成績排名,這個語法確定可運作

SELECT
t1.id,
t1.name,
count(distinct(stage)) as total_stage,
count(rounds) as total_rounds,
sum(score) as total_score,
sum(strike) as total_strike,
max(score) as max_score,
max(strike) as max_strike,
avg(score) as avg_score
FROM `bowler` as t1 left join `rounds` as t2 on t1.id = t2.player
LEFT JOIN `stage` as t3 ON t2.stage = t3.id
WHERE
DATE( `date` ) BETWEEN '2015-01-01' AND '2015-12-31'
group by t1.id
order by avg_score desc

現在我想加上一個條件,只列出參賽局數大於 100 局的隊員,如果直接使用 WHERE 加上條件

SELECT
t1.id,
t1.name,
count(distinct(stage)) as total_stage,
count(rounds) as total_rounds,
sum(score) as total_score,
sum(strike) as total_strike,
max(score) as max_score,
max(strike) as max_strike,
avg(score) as avg_score
FROM `bowler` as t1 left join `rounds` as t2 on t1.id = t2.player
LEFT JOIN `stage` as t3 ON t2.stage = t3.id
WHERE
DATE( `date` ) BETWEEN '2015-01-01' AND '2015-12-31'
AND
total_rounds > 100
group by t1.id
order by avg_score desc

以上會返回錯誤 Unknown Column ‘total_rounds’ in where clause,無法辨識別名 total_rounds

當有 GROUP BY 時,若需要加上條件,改用 HAVING 語句,如下

SELECT
t1.id,
t1.name,
count(distinct(stage)) as total_stage,
count(rounds) as total_rounds,
sum(score) as total_score,
sum(strike) as total_strike,
max(score) as max_score,
max(strike) as max_strike,
avg(score) as avg_score
FROM `bowler` as t1 left join `rounds` as t2 on t1.id = t2.player
LEFT JOIN `stage` as t3 ON t2.stage = t3.id
WHERE DATE( `date` ) BETWEEN '2015-01-01' AND '2015-12-31'
group by t1.id
HAVING total_rounds > 100
order by avg_score desc

這樣就可以抓出 2015 年度,參賽局數大於 100 局的隊員資料了