SQL - 在 Group By 中使用别名

SQL - using alias in Group By

提问by Haoest

Just curious about SQL syntax. So if I have

只是对 SQL 语法感到好奇。所以如果我有

SELECT 
 itemName as ItemName,
 substring(itemName, 1,1) as FirstLetter,
 Count(itemName)
FROM table1
GROUP BY itemName, FirstLetter

This would be incorrect because

这是不正确的，因为

GROUP BY itemName, FirstLetter 

really should be

真的应该

GROUP BY itemName, substring(itemName, 1,1)

But why can't we simply use the former for convenience?

但是为什么我们不能简单地使用前者来方便呢？

回答by Codo

SQL is implemented as if a query was executed in the following order:

SQL 的实现就像按以下顺序执行查询一样：

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

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

For most relational database systems, this order explains which names (columns or aliases) are valid because they must have been introduced in a previous step.

对于大多数关系数据库系统，此顺序解释了哪些名称（列或别名）是有效的，因为它们必须在上一步中引入。

So in Oracle and SQL Server, you cannot use a term in the GROUP BY clause that you define in the SELECT clause because the GROUP BY is executed before the SELECT clause.

因此，在 Oracle 和 SQL Server 中，您不能在 SELECT 子句中定义的 GROUP BY 子句中使用术语，因为 GROUP BY 在 SELECT 子句之前执行。

There are exceptions though: MySQL and Postgres seem to have additional smartness that allows it.

但也有例外：MySQL 和 Postgres 似乎具有允许它的额外智能。

回答by Chris Shaffer

You could always use a subquery so you can use the alias; Of course, check the performance (Possible the db server will run both the same, but never hurts to verify):

你总是可以使用子查询，这样你就可以使用别名；当然，检查性能（可能 db 服务器将运行相同，但永远不会伤害验证）：

SELECT ItemName, FirstLetter, COUNT(ItemName)
FROM (
    SELECT ItemName, SUBSTRING(ItemName, 1, 1) AS FirstLetter
    FROM table1
    ) ItemNames
GROUP BY ItemName, FirstLetter

回答by Bill Gribble

At least in PostgreSQL you can use the column number in the resultset in your GROUP BY clause:

至少在 PostgreSQL 中，您可以在 GROUP BY 子句中使用结果集中的列号：

SELECT 
 itemName as ItemName,
 substring(itemName, 1,1) as FirstLetter,
 Count(itemName)
FROM table1
GROUP BY 1, 2

Of course this starts to be a pain if you are doing this interactively and you edit the query to change the number or order of columns in the result. But still.

当然，如果您以交互方式执行此操作并编辑查询以更改结果中列的数量或顺序，这将开始变得很痛苦。但是还是。

回答by bobs

SQL Server doesn't allow you to reference the alias in the GROUP BY clause because of the logical order of processing. The GROUP BY clause is processed before the SELECT clause, so the alias is not known when the GROUP BY clause is evaluated. This also explains why you can use the alias in the ORDER BY clause.

由于处理的逻辑顺序，SQL Server 不允许您在 GROUP BY 子句中引用别名。GROUP BY 子句在 SELECT 子句之前处理，因此在评估 GROUP BY 子句时不知道别名。这也解释了为什么可以在 ORDER BY 子句中使用别名。

Here is one source for information on the SQL Server logical processing phases.

这是有关SQL Server 逻辑处理阶段的信息来源之一。

回答by Ricardo

I'm not answering why it is so, but only wanted to show a way around that limitation in SQL Server by using CROSS APPLYto create the alias. You then use it in the GROUP BYclause, like so:

我没有回答为什么会这样，而只是想通过使用CROSS APPLY来创建别名来展示解决 SQL Server 中该限制的方法。然后在GROUP BY子句中使用它，如下所示：

SELECT 
 itemName as ItemName,
 FirstLetter,
 Count(itemName)
FROM table1
CROSS APPLY (SELECT substring(itemName, 1,1) as FirstLetter) Alias
GROUP BY itemName, FirstLetter

回答by Shannon S

Caution that using alias in the Group By (for services that support it, such as postgres) can have unintended results. For example, if you create an alias that already exists in the inner statement, the Group By will chose the inner field name.

注意在 Group By 中使用别名（对于支持它的服务，例如 postgres）可能会产生意想不到的结果。例如，如果您创建的别名已存在于内部语句中，则 Group By 将选择内部字段名称。

-- Working example in postgres
select col1 as col1_1, avg(col3) as col2_1
from
    (select gender as col1, maritalstatus as col2, 
    yearlyincome as col3 from customer) as layer_1
group by col1_1;

-- Failing example in postgres
select col2 as col1, avg(col3)
from
    (select gender as col1, maritalstatus as col2,
    yearlyincome as col3 from customer) as layer_1
group by col1;

回答by mechanical_meat

Some DBMSs will let you use an alias instead of having to repeat the entire expression.
Teradata is one such example.

某些 DBMS 允许您使用别名而不必重复整个表达式。
Teradata 就是这样一个例子。

I avoid ordinal position notation as recommended by Bill for reasons documented in this SO question.

出于此 SO 问题中记录的原因，我避免使用 Bill 建议的序数位置符号。

The easy and robust alternative is to always repeat the expression in the GROUP BY clause.
DRY does NOT apply to SQL.

简单而可靠的替代方法是始终重复 GROUP BY 子句中的表达式。
DRY 不适用于 SQL。

回答by GGGforce

Beware of using aliases when grouping the results from a view in SQLite. You will get unexpected results if the alias name is the same as the column name of any underlying tables (to the views.)

在对 SQLite 视图中的结果进行分组时，请注意使用别名。如果别名与任何基础表（对于视图）的列名相同，您将得到意想不到的结果。

回答by Bob Jarvis - Reinstate Monica

Back in the day I found that Rdb, the former DEC product now supported by Oracle allowed the column alias to be used in the GROUP BY. Mainstream Oracle through version 11 does not allow the column alias to be used in the GROUP BY. Not sure what Postgresql, SQL Server, MySQL, etc will or won't allow. YMMV.

回到那天，我发现 Rdb（现在由 Oracle 支持的前 DEC 产品）允许在 GROUP BY 中使用列别名。到版本 11 的主流 Oracle 不允许在 GROUP BY 中使用列别名。不确定 Postgresql、SQL Server、MySQL 等将允许或不允许的内容。天啊。