GROUP BY子句的别名? [英] Alias for GROUP BY clause?

问题描述

在MySQL中，可以在SELECT子句中创建的GROUP BY子句中使用别名(参见

In MySQL, it is possible to use an alias in GROUP BY clause that has been created in SELECT clause (Cf. MySQL Reference).

我只是想知道为什么无法在GROUP BY中创建别名并在SELECT中使用它，它遵循SELECT语句的执行顺序.

I am just wondering why it is not possible to create an alias in GROUP BY and use it in SELECT, which follows the execution order of SELECT statement.

换句话说，以下为什么不合语法?

In other words, why is the following ungrammatical?

SELECT region, SUM(population)
  FROM population_us_states
 GROUP BY
       CASE state_name
               WHEN 'CT' THEN 'New England'
               WHEN 'RI' THEN 'New England'
               WHEN 'MA' THEN 'New England'
               WHEN 'ME' THEN 'New England'
               WHEN 'NH' THEN 'New England'
               WHEN 'VT' THEN 'New England'
               WHEN 'CA' THEN 'West Coast'
               WHEN 'OR' THEN 'West Coast'
               WHEN 'WA' THEN 'West Coast'
        ELSE 'other' END AS region;

推荐答案

使用嵌套的SELECT-内部的一个提取总体并生成计算的列，然后外部的GROUP BY可以将该列GROUP BY:

Use a nested SELECT - the inner one to extract the population and generate the calculated column, and the outer one can then GROUP BY that column:

SELECT region, SUM(population) FROM (

   SELECT CASE ...

   ... AS region, population FROM population_us_states )

GROUP BY region

这篇关于GROUP BY子句的别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！