为什么我不能分组1当ORDER BY 1可以？

Why are column ordinals legal for ORDER BY but not for GROUP BY? That is, can anyone tell me why this query

为什么列顺序对ORDER BY合法，但对GROUP BY不合法？也就是说，任何人都可以告诉我为什么这个查询

SELECT OrgUnitID, COUNT(*) FROM Employee AS e GROUP BY OrgUnitID

cannot be written as

不能写成

SELECT OrgUnitID, COUNT(*) FROM Employee AS e GROUP BY 1

When it's perfectly legal to write a query like

当编写像这样的查询是完全合法的

SELECT OrgUnitID FROM Employee AS e ORDER BY 1

？

I'm really wondering if there's something subtle about the relational calculus, or something, that would prevent the grouping from working right.

我真的想知道关系微积分是否存在一些微妙的东西，这会阻止分组正常工作。

The thing is, my example is pretty trivial. It's common that the column that I want to group by is actually a calculation, and having to repeat the exact same calculation in the GROUP BY is (a) annoying and (b) makes errors during maintenance much more likely. Here's a simple example:

问题是，我的例子非常简单。通常我想要分组的列实际上是一个计算，并且必须在GROUP BY中重复完全相同的计算是（a）烦人和（b）更有可能在维护期间产生错误。这是一个简单的例子：

SELECT DATEPART(YEAR,LastSeenOn), COUNT(*)
    FROM Employee AS e
    GROUP BY DATEPART(YEAR,LastSeenOn)

I would think that SQL's rule of normalize to only represent data once in the database ought to extend to code as well. I'd want to only right that calculation expression once (in the SELECT column list), and be able to refer to it by ordinal in the GROUP BY.

我认为SQL的规范化规则只能在数据库中表示一次，也应该扩展到代码。我只想对该计算表达式进行一次修改（在SELECT列列表中），并且能够通过GROUP BY中的序数引用它。

Clarification: I'm specifically working on SQL Server 2008, but I wonder about an overall answer nonetheless.

澄清：我是专门研究SQL Server 2008的，但我想知道总体答案。

4 个解决方案

#1

One of the reasons is because ORDER BY is the last thing that runs in a SQL Query, here is the order of operations

其中一个原因是因为ORDER BY是SQL Query中运行的最后一件事，这是操作的顺序

FROM clause
FROM子句
WHERE clause
WHERE子句
GROUP BY clause
GROUP BY子句
HAVING clause
HAVING子句
SELECT clause
SELECT子句
ORDER BY clause
ORDER BY子句

so once you have the columns from the SELECT clause you can use ordinal positioning

因此，一旦获得SELECT子句中的列，就可以使用序数定位

EDIT, added this based on the comment Take this for example

编辑，根据评论添加这个以此为例

create table test (a int, b int)
insert test values(1,2)
go

The query below will parse without a problem, it won't run

下面的查询将解析没有问题，它将无法运行

select a as b, b as a
     from test
    order by 6

here is the error

这是错误

Msg 108, Level 16, State 1, Line 3
The ORDER BY position number 6 is out of range of the number of items in the select list.

消息108，级别16，状态1，行3 ORDER BY位置编号6超出了选择列表中项目数的范围。

This also parses fine

这也解析得很好

select a as b, b as a
     from test
    group by 1

But it blows up with this error

但它会因为这个错误而爆炸

Msg 164, Level 15, State 1, Line 3
Each GROUP BY expression must contain at least one column that is not an outer reference.

消息164，级别15，状态1，行3每个GROUP BY表达式必须包含至少一个不是外部引用的列。

#2

There is a lot of elementary inconsistencies in SQL, and use of scalars is one of them. For example, anyone might expect

SQL中存在许多基本的不一致性，使用标量就是其中之一。例如，任何人都可能期望


    select * from countries
    order by 1

and

和


    select * from countries
    order by 1.00001

to be a similar queries (the difference between the two can be made infinitesimally small, after all), which are not.

是一个类似的查询（毕竟两者之间的差异可以无限小），但事实并非如此。

#3

I'm not sure if the standard specifies if it is valid, but I believe it is implementation-dependent. I just tried your first example with one SQL engine, and it worked fine.

我不确定标准是否指定它是否有效，但我认为它是依赖于实现的。我刚用一个SQL引擎尝试了你的第一个例子，它运行正常。

#4

use aliasses :

使用别名：

SELECT DATEPART(YEAR,LastSeenOn) as 'seen_year', COUNT(*) as 'count'
    FROM Employee AS e
    GROUP BY 'seen_year'

** EDIT **

**编辑**

if GROUP BY alias is not allowed for you, here's a solution / workaround:

如果你不允许使用GROUP BY别名，这里有一个解决方案/解决方法：

SELECT seen_year
     , COUNT(*) AS Total 
  FROM (
    SELECT DATEPART(YEAR,LastSeenOn) as seen_year, *
    FROM Employee AS e
  ) AS inline_view
GROUP 
    BY seen_year

#1