如何对 MS-SQL Server 中的别名列执行 GROUP BY？

How do I perform a GROUP BY on an aliased column in MS-SQL Server?

提问by Gavin Miller

I'm trying to perform a group byaction on an aliased column (example below) but can't determine the proper syntax.

我正在尝试对别名列（下面的示例）执行分组操作，但无法确定正确的语法。

SELECT       LastName + ', ' + FirstName AS 'FullName'
FROM         customers
GROUP BY     'FullName'

What is the correct syntax?

什么是正确的语法？

---

---

EDIT编辑

Extending the question further (I had not expected the answers I had received) would the solution still apply for a CASEed aliased column?

进一步扩展问题（我没想到我收到了答案）该解决方案是否仍适用于 CASEed 别名列？

SELECT       
    CASE
        WHEN LastName IS NULL THEN FirstName
        WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName
    END AS 'FullName'
FROM         customers
GROUP BY     
    LastName, FirstName

And the answer is yes it does still apply.

答案是肯定的，它仍然适用。

回答by cmsjr

You pass the expression you want to group by rather than the alias

您传递要分组的表达式而不是别名

SELECT       LastName + ', ' + FirstName AS 'FullName'
FROM         customers
GROUP BY      LastName + ', ' + FirstName

回答by Amy B

This is what I do.

这就是我所做的。

SELECT FullName
FROM
(
  SELECT LastName + ', ' + FirstName AS FullName
  FROM customers
) as sub
GROUP BY FullName

---

---

This technique applies in a straightforward way to your "edit" scenario:

这种技术以一种直接的方式应用于您的“编辑”场景：

SELECT FullName
FROM
(
  SELECT
     CASE
       WHEN LastName IS NULL THEN FirstName
       WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName
     END AS FullName
  FROM customers
) as sub
GROUP BY FullName

回答by James Orr

Unfortunately you can't reference your alias in the GROUP BY statement, you'll have to write the logic again, amazing as that seems.

不幸的是，您不能在 GROUP BY 语句中引用您的别名，您必须再次编写逻辑，这看起来很神奇。

SELECT       LastName + ', ' + FirstName AS 'FullName'
FROM         customers
GROUP BY     LastName + ', ' + FirstName

Alternately you could put the select into a subselect or common table expression, after which you could group on the column name (no longer an alias.)

或者，您可以将选择放入子选择或公共表表达式中，之后您可以对列名（不再是别名）进行分组。

回答by Michael Buen

Sorry, this is not possible with MS SQL Server (possible though with PostgreSQL):

抱歉，这在 MS SQL Server 中是不可能的（尽管在 PostgreSQL 中可能）：

select lastname + ', ' + firstname as fullname
from person
group by fullname

Otherwise just use this:

否则就用这个：

select x.fullname
from 
(
    select lastname + ', ' + firstname as fullname
    from person
) as x
group by x.fullname

Or this:

或这个：

select lastname + ', ' + firstname as fullname
from person
group by lastname, firstname  -- no need to put the ', '

The above query is faster, groups the fields first, then computethose fields.

上面的查询更快，首先对字段进行分组，然后计算这些字段。

The following query is slower (it tries to computefirst the select expression, then it groups the records based on that computation).

以下查询较慢（它尝试首先计算选择表达式，然后根据该计算对记录进行分组）。

select lastname + ', ' + firstname as fullname
from person
group by lastname + ', ' + firstname

回答by Jon Ericson

My guess is:

我的猜测是：

SELECT       LastName + ', ' + FirstName AS 'FullName'
FROM         customers
GROUP BY     LastName + ', ' + FirstName

Oracle has a similar limitation, which is annoying. I'm curious if there exists a better solution.

Oracle 也有类似的限制，很烦人。我很好奇是否有更好的解决方案。

To answer the second half of the question, this limitation applies to more complex expressions such as your case statement as well. The best suggestion I've seen it to use a sub-select to name the complex expression.

为了回答问题的后半部分，此限制也适用于更复杂的表达式，例如 case 语句。我见过的最好的建议是使用子选择来命名复杂的表达式。

回答by Bill Karwin

Given your edited problem description, I'd suggest using COALESCE()instead of that unwieldy CASEexpression:

鉴于您编辑过的问题描述，我建议使用COALESCE()而不是那个笨拙的CASE表达：

SELECT FullName
FROM (
  SELECT COALESCE(LastName+', '+FirstName, FirstName) AS FullName
  FROM customers
) c
GROUP BY FullName;

回答by Ricardo

You can use CROSS APPLYto create an alias and use it in the GROUP BYclause, like so:

您可以使用CROSS APPLY来创建别名并在GROUP BY子句中使用它，如下所示：

SELECT       FullName
FROM         Customers
CROSS APPLY  (SELECT LastName + ', ' + FirstName AS FullName) Alias
GROUP BY     FullName

回答by MatBailie

SELECT       
    CASE
        WHEN LastName IS NULL THEN FirstName
        WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName
    END AS 'FullName'
FROM
    customers
GROUP BY     
    LastName,
    FirstName

This works because the formula you use (the CASE statement) can never give the same answer for two different inputs.

这是有效的，因为您使用的公式（CASE 语句）永远不会对两个不同的输入给出相同的答案。

This is not the case if you used something like:

如果您使用了以下内容，则情况并非如此：

LEFT(FirstName, 1) + ' ' + LastName

In such a case "James Taylor" and "John Taylor" would both result in "J Taylor".

在这种情况下，“James Taylor”和“John Taylor”都会导致“J Taylor”。

If you wanted your output to have "J Taylor" twice (one for each person):

如果你想让你的输出有两次“J Taylor”（每个人一个）：

GROUP BY LastName, FirstName

If, however, you wanted just one row of "J Taylor" you'd want:

但是，如果您只想要一排“J Taylor”：

GROUP BY LastName, LEFT(FirstName, 1)

回答by JeffO

If you want to avoid the mess of the case statement being in your query twice, you may want to place it in a User-Defined-Function.

如果您想避免在您的查询中出现两次 case 语句的混乱，您可能希望将其放在用户定义的函数中。

Sorry, but SQL Server would not render the dataset before the Group By clause so the column alias is not available. You could use it in the Order By.

抱歉，SQL Server 不会在 Group By 子句之前呈现数据集，因此列别名不可用。您可以在 Order By 中使用它。

回答by Deepak Pathak

SELECT 
CASE WHEN LastName IS NULL THEN FirstName         
     WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName     
END AS 'FullName' 
FROM  customers GROUP BY 1`