SQL Server 2005 按别名分组

Question

我有

 select CustID,

case permission
When 'read' then 'X'
end as 'Read',

case permission
when 'write' then 'X'
end as 'Write',

case permission
when 'own' then 'X'
end as 'Own',

case permission
when ‘destroy’ then ‘X’
end as ‘Destroy’

from rights

group by custID, permission

我只想要每个 custID 一行。相反，我得到:

CustID    |  Read | Write |  Own | Destroy

208345482     X     NULL    NULL   NULL| 
208345482    NULL   NULL     X     NULL|
208345482    NULL   NULL    NULL      X|
208345482    NULL    X      NUL    NULL|
8093657522   NULL   NULL    NULL     X|
8093657522   NULL   NULL     X    NULL|

我试图按别名子查询分组但无济于事。任何帮助将不胜感激。

Answer 1

您不能在 GROUP BY 中使用别名因为它在应用别名之前被解析。唯一可以使用别名的地方是 ORDER BY子句，因为它是在 SELECT 之后解析的唯一子句定义别名的地方。

典型的解决方法是重复 GROUP BY 中的表达式。条款:

SELECT x = CASE ... END, SUM(something)
  FROM dbo.somewhere
  GROUP BY CASE ... END;

或者使用 CTE:

;WITH src AS
(
  SELECT x = CASE ... END, something
    FROM dbo.somewhere
)
SELECT x, SUM(something)
  FROM src
  GROUP BY x;

或者使用子查询:

SELECT x, SUM(something)
  FROM
  (
    SELECT x = CASE ... END, something
      FROM dbo.somewhere
  ) AS src
  GROUP BY x;

这些都应该以类似的方式优化，但您应该测试您的特定场景以确保。

在你的情况下，你可以这样写:

SELECT CustID,
  [Read]    = MAX(CASE permission WHEN 'read'    THEN 'X' END),
  [Write]   = MAX(CASE permission WHEN 'write'   THEN 'X' END),
  [Own]     = MAX(CASE permission WHEN 'own'     THEN 'X' END),
  [Destroy] = MAX(CASE permission WHEN 'destroy' THEN 'X' END)
FROM dbo.rights
GROUP BY custID;

或者像这样:

SELECT CustID,
  MAX([Read]), MAX([Write]), MAX([Own]), MAX([Destroy])
FROM
(
  SELECT CustID,
   [Read]    = CASE permission WHEN 'read'    THEN 'X' END,
   [Write]   = CASE permission WHEN 'write'   THEN 'X' END,
   [Own]     = CASE permission WHEN 'own'     THEN 'X' END,
   [Destroy] = CASE permission WHEN 'destroy' THEN 'X' END
  FROM dbo.rights
) AS src
GROUP BY custID;

请不要使用单引号分隔列或表 'alias' .不仅不推荐使用语法，而且还使实体看起来像字符串文字。如果必须对其进行转义(例如，如果您选择了错误的列名)，请使用 [square brackets] .