blocks|key|913953|text|您可以始终使用子查询，这样您就可以使用别名；当然，请检查性能(可能数据库服务器将同时运行这两个查询，但验证起来不会有什么问题)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|913954|SELECT+ItemName,+FirstLetter,+COUNT(ItemName)
FROM+(
++++SELECT+ItemName,+SUBSTRING(ItemName,+1,+1)+AS+FirstLetter
++++FROM+table1
++++)+ItemNames
GROUP+BY+ItemName,+FirstLetter|code-block|syntax|javascript|913955|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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):

<pre><code>SELECT ItemName, FirstLetter, COUNT(ItemName)
FROM (
 SELECT ItemName, SUBSTRING(ItemName, 1, 1) AS FirstLetter
 FROM table1
 ) ItemNames
GROUP BY ItemName, FirstLetter
</code></pre>

blocks|key|1128649|text|至少在PostgreSQL中，您可以在GROUP+BY子句的结果集中使用列号：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1128650|SELECT+
+itemName+as+ItemName,
+substring(itemName,+1,1)+as+FirstLetter,
+Count(itemName)
FROM+table1
GROUP+BY+1,+2|code-block|syntax|javascript|1128651|当然，如果您以交互方式执行此操作，并且您编辑查询以更改结果中列的数量或顺序，那么这将是一件痛苦的事情。不过，还是这样。|1128652|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

<pre><code>SELECT 
 itemName as ItemName,
 substring(itemName, 1,1) as FirstLetter,
 Count(itemName)
FROM table1
GROUP BY 1, 2
</code></pre>

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.

blocks|key|914055|text|由于处理的逻辑顺序，SQL+Server不允许您在GROUP+BY子句中引用别名。GROUP+BY子句是在SELECT子句之前处理的，因此在计算GROUP+BY子句时，别名是未知的。这也解释了为什么可以在ORDER+BY子句中使用别名。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|914056|这里有一个关于SQL+Server+logical+processing+phases的信息来源。|offset|length|914057|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-%25E2%2580%2593-logical-query-processing-phases-%25E2%2580%2593-order-of-statement-execution/^0|0|7|10|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

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.

Here is one source for information on the <a href="http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-%E2%80%93-logical-query-processing-phases-%E2%80%93-order-of-statement-execution/" rel="noreferrer">SQL Server logical processing phases</a>.

blocks|key|1129035|text|我并不是在回答为什么会这样，只是想通过使用CROSS+APPLY创建别名来展示一种绕过SQL+Server中这一限制的方法。然后在GROUP+BY子句中使用它，如下所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1129036|SELECT+
+itemName+as+ItemName,
+FirstLetter,
+Count(itemName)
FROM+table1
CROSS+APPLY+(SELECT+substring(itemName,+1,1)+as+FirstLetter)+Alias
GROUP+BY+itemName,+FirstLetter|code-block|syntax|javascript|1129037|entityMap^0|L|B|1T|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

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

<pre><code>SELECT 
 itemName as ItemName,
 FirstLetter,
 Count(itemName)
FROM table1
CROSS APPLY (SELECT substring(itemName, 1,1) as FirstLetter) Alias
GROUP BY itemName, FirstLetter
</code></pre>

blocks|key|1128988|text|注意，在Group+By+(对于支持别名的服务，如postgres)中使用别名可能会产生意外的结果。例如，如果创建的别名已存在于inner语句中，则Group+By将选择内部字段名称。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1128989|--+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;|code-block|syntax|javascript|1128990|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>-- 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;
</code></pre>

blocks|key|1128738|text|一些DBMS将允许您使用别名，而不必重复整个表达式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1128739|Teradata就是这样一个例子。|1128740|出于this+SO+question中记录的原因，我避免使用Bill推荐的顺序位置表示法。|offset|length|1128741|一种简单而健壮的替代方法是始终重复GROUP+BY子句中的表达式。|1128742|DRY不适用于SQL。|1128743|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/2253040/benefits-of-using-sql-ordinal-position-notation^0|0|0|2|G|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@$F|W|G|X|1|Y]]|A|$]]|$1|H|3|I|5|6|7|Z|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|10|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]]]

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

I avoid ordinal position notation as recommended by Bill for reasons documented in <a href="https://stackoverflow.com/questions/2253040/benefits-of-using-sql-ordinal-position-notation">this SO question</a>.

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

blocks|key|914098|text|在对SQLite中的视图结果进行分组时，请注意不要使用别名。如果别名与任何底层表(对于视图)的列名相同，您将得到意外的结果。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|914099|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.)

blocks|key|913997|text|以前，我发现Rdb允许在GROUP+BY中使用列别名。主流Oracle版本11不允许在GROUP+BY中使用列别名。不确定Postgresql，SQL+Server，MySQL等会或不会允许什么。YMMV.|type|unstyled|depth|inlineStyleRanges|entityRanges|data|913998|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1129073|text|至少在Postgres中，可以在group+by子句中使用别名：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1129074|SELECT+itemName作为ItemName1，substring(itemName，1,1)作为FirstLetter，Count(itemName)+FROM+table1+GROUP+BY+ItemName1，FirstLetter；|1129075|我不建议将别名重命名为大小写的更改，这会造成混淆。|1129076|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

In at least Postgres, you can use the alias name in the group by clause:
SELECT
itemName as ItemName1,
substring(itemName, 1,1) as FirstLetter,
Count(itemName)
FROM table1
GROUP BY ItemName1, FirstLetter;
I wouldn't recommend renaming an alias as a change in capitalization, that causes confusion.

Just curious about SQL syntax. So if I have

<pre><code>SELECT 
 itemName as ItemName,
 substring(itemName, 1,1) as FirstLetter,
 Count(itemName)
FROM table1
GROUP BY itemName, FirstLetter
</code></pre>

This would be incorrect because 

<pre><code>GROUP BY itemName, FirstLetter 
</code></pre>

really should be 

<pre><code>GROUP BY itemName, substring(itemName, 1,1)
</code></pre>

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

SQL - using alias in Group By

SQL Server

PostgreSQL

数据库

服务器

SQLite

MySQL

只是对SQL语法很好奇。所以如果我有SELECT  itemName as ItemName, substring(itemName, 1,1) as FirstLetter, Count(itemName)FROM table1GROUP BY itemName, FirstLetter这是不正确的，因为GROUP BY itemName, FirstLetter 真的应该是GROUP BY i

问SQL -在Group By中使用别名
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL -在Group By中使用别名EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL -在Group By中使用别名
EN