mysql having where 别名 相对位置

小结：

1) where having 的过滤范围

having  group by 、聚合函数

where select 字段

2）别名支持的位置

• The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses.

3）

 a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. 

 

MySQL :: MySQL 8.0 Reference Manual :: 15.2.13 SELECT Statement https://dev.mysql.com/doc/refman/8.0/en/select.html

 

• The WHERE clause specifies conditions on columns in the select list, but cannot refer to aggregate functions. The HAVING clause specifies conditions on groups, typically formed by the GROUP BY clause. 

 

• GROUP BY permits a WITH ROLLUP modifier. See Section 14.19.2, “GROUP BY Modifiers”.

  Previously, it was not permitted to use ORDER BY in a query having a WITH ROLLUP modifier. This restriction is lifted as of MySQL 8.0.12. See Section 14.19.2, “GROUP BY Modifiers”.

• The HAVING clause, like the WHERE clause, specifies selection conditions. The WHERE clause specifies conditions on columns in the select list, but cannot refer to aggregate functions. The HAVING clause specifies conditions on groups, typically formed by the GROUP BY clause. The query result includes only groups satisfying the HAVING conditions. (If no GROUP BY is present, all rows implicitly form a single aggregate group.)

  The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

  The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

  If the HAVING clause refers to a column that is ambiguous, a warning occurs. In the following statement, col2 is ambiguous because it is used as both an alias and a column name:

  Press CTRL+C to copy

   

  SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

  Preference is given to standard SQL behavior, so if a HAVING column name is used both in GROUP BY and as an aliased column in the select column list, preference is given to the column in the GROUP BY column.

• Do not use HAVING for items that should be in the WHERE clause. For example, do not write the following:

  Press CTRL+C to copy

   

  SELECT col_name FROM tbl_name HAVING col_name > 0;

  Write this instead:

  Press CTRL+C to copy

   

  SELECT col_name FROM tbl_name WHERE col_name > 0;

• The HAVING clause can refer to aggregate functions, which the WHERE clause cannot:

  Press CTRL+C to copy

   

  SELECT user, MAX(salary) FROM users
    GROUP BY user HAVING MAX(salary) > 10;

  (This did not work in some older versions of MySQL.)

• MySQL permits duplicate column names. That is, there can be more than one select_expr with the same name. This is an extension to standard SQL. Because MySQL also permits GROUP BY and HAVING to refer to select_expr values, this can result in an ambiguity:

  Press CTRL+C to copy

   

  SELECT 12 AS a, a FROM t GROUP BY a;

  In that statement, both columns have the name a. To ensure that the correct column is used for grouping, use different names for each select_expr.

• The WINDOW clause, if present, defines named windows that can be referred to by window functions. For details, see Section 14.20.4, “Named Windows”.

• MySQL resolves unqualified column or alias references in ORDER BY clauses by searching in the select_expr values, then in the columns of the tables in the FROM clause. For GROUP BY or HAVING clauses, it searches the FROM clause before searching in the select_expr values. (For GROUP BY and HAVING, this differs from the pre-MySQL 5.0 behavior that used the same rules as for ORDER BY.) 

 

 

翻译

搜索

复制