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

我正在尝试对别名列执行分组操作(下面的示例),但无法确定正确的语法.

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

什么是正确的语法？

---

编辑

进一步扩展问题(我没想到我收到的答案)解决方案是否仍然适用于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

答案是肯定的,它仍然适用.

1> cmsjr..：

---

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

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

---

你应该保留它,至少要区分'x,yz'和'xy,z',它们会在没有逗号的情况下汇总到相同的字符串.

---

@ConcernedOfTunbridgeWells六年后,我现在意识到我的建议混淆了删除','来自,当我说"只是删除','在group by子句中",我的意思是只是删除串联GROUP BY,然后只按其实体(lastname和firstname)对事物进行分组,而不是从SELECT中删除连接; 事实上我[提到](http://stackoverflow.com/questions/497241/how-do-i-perform-a-group-by-on-an-aliased-column-in-ms-sql-server/497263 #comment313078_497251)在第二句中我想要实现的目标.

2> Amy B..：

---

这就是我的工作.

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

---

此技术以直接的方式应用于"编辑"方案:

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

3> James Orr..：

---

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

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

或者,您可以将select放入子选择表或公用表表达式,之后您可以对列名称进行分组(不再是别名.)

4> Michael Buen..：

---

抱歉,使用MS SQL Server是不可能的(虽然使用PostgreSQL可能):

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

否则只需使用:

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

或这个:

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

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

以下查询较慢(它尝试首先计算选择表达式,然后根据该计算对记录进行分组).

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