MySQL别名是否能在group by语句中使用？ -- 官方文档解读

一般来说，SQL自己的运行顺序是：

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

因此，如果在group by之后写了字段别名，会报错。

但是在MySQL、Postgres 和 Hive里是允许把别名放在GROUP BY之后的，运行顺序被优化成：

1. FROM clause
2. WHERE clause
3. SELECT clause
4. GROUP BY clause
5. HAVING clause
6. SELECT clause
7. ORDER BY clause

不论是不是窗口函数，所有代码实际上都被select了两次。

官方的原文是：

In MySQL, you can use the column alias in the ORDER BY, GROUP BY and HAVING clauses to refer to the column.

Notice that you cannot use a column alias in the WHERE clause. The reason is that when MySQL evaluates the WHERE clause, the values of columns specified in the SELECT clause are not be evaluated yet.

官方给出的代码是：

SELECT
    orderNumber `Order no.`,
    SUM(priceEach * quantityOrdered) total
FROM
    orderDetails
GROUP BY
    `Order no.`
HAVING
    total > 60000;