mysql - SQL语句中having为什么可以操作as声明的别名？

Answer 1

I have considered this issue before.
The conclusion is: the information provided on the Internet is roughly for the entire sql, not for mysql.

The processing method of mysql is to generate a virtual table (or temporary table) in the middle, and the generated columns of this virtual table rely on select, so I guess it is similar to the operation after having. In fact, the virtual table has been generated internally based on select. The columns are naturally followed by as.

Answer 2

I remember where is filtered before select query, and having is filtered after select

Answer 3

Running and parsing are two different things. When the alias declared as is recognized, it can only be said to be parsed. The select keyword running sequence is already running after having.

Answer 4

MSSQL cannot use aliases in HAVING, but MYSQL can. By viewing the execution plan, you can see that the two processing methods are different, as follows:
MSSQL:

MYSQL:

Answer 5

sqlserver

1: Logical execution sequence of the query

(1) FROM < left_table>

(3) < join_type> JOIN < right_table> (2) ON < join_condition>

(4) WHERE < where_condition>

(5) GROUP BY < group_by_list>

(6) WITH {cube | rollup}

(7) HAVING < having_condition>

(8) SELECT (9) DISTINCT (11) < top_specification> < select_list>

(10) ORDER BY < order_by_list>

The standard SQL parsing order is:

(1).FROM clause assembles data from different data sources

(2).WHERE clause filters records based on specified conditions

(3).GROUP BY clause divides the data into multiple groups

(4). Use aggregate functions for calculations

(5). Use HAVING clause to filter groups

(6). Calculate all expressions

(7). Use ORDER BY to sort the result set

2 Execution order:

1.FROM: Perform Cartesian product on the first two tables in the FROM clause to generate virtual table vt1

2.ON: Apply the ON filter to the vt1 table and only rows that satisfy < join_condition> are true will be inserted into vt2

3.OUTER(join): If OUTER JOIN is specified, rows not found in the preserved table will be added to vt2 as external rows to generate t3. If from contains more than two tables, the result generated by the previous join will be generated. Repeat the steps for the table and the next table and end directly

4.WHERE: Apply WHERE filter to vt3. Only rows with <where_condition> set to true are inserted into vt4

5.GROUP BY: Group the rows in vt4 by the column list in the GROUP BY clause to generate vt5

6.CUBE|ROLLUP: Insert supergroups into vt6 to generate vt6

7.HAVING: Apply HAVING filter to vt6. Only groups with <having_condition> set to true are inserted into vt7

8.SELECT: Process the select list to generate vt8

9.DISTINCT: Remove duplicate rows from vt8 to generate vt9

10.ORDER BY: Sort the rows of vt9 according to the column list in the order by clause to generate a cursor vc10

11.TOP: Select the specified number or proportion of rows from the beginning of vc10 to generate vt11 and return to the caller