SQL Server 2005 Group by别名

I have

 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

I just want one line for each custID. Instead I get:

我只想为每个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|

I have tried to group by aliased subqueries to no avail. Any assistance would be greatly appreciated.

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

2 个解决方案

#1

You can't use an alias in GROUP BY because it is parsed before the alias is applied. The only place you can use the alias is in the ORDER BY clause, since it is the only clause parsed after SELECT where the aliases are defined.

您不能在GROUP BY中使用别名,因为在应用别名之前会对其进行解析。您可以使用别名的唯一位置是ORDER BY子句,因为它是在SELECT之后解析的唯一子句,其中定义了别名。

Typical workarounds are either repeating the expression in the GROUP BY clause:

典型的解决方法是重复GROUP BY子句中的表达式:

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

Or using a CTE:

或者使用CTE:

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

Or using a subquery:

或者使用子查询:

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

These should all optimize similarly but you should test your specific scenario to be sure.

这些都应该同样优化,但您应该测试您的具体方案,以确保。

In your case you can write this as follows:

在您的情况下,您可以这样写:

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;

Or like this:

或者像这样:

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;

Please don't use single quotes to delimit a column or table 'alias'. Not only is the syntax deprecated but it also makes the entity look like a string literal. If it must be escaped (e.g. if you choose bad column names), use [square brackets].

请不要使用单引号来分隔列或表的“别名”。语法不仅被弃用,而且还使实体看起来像字符串文字。如果必须对其进行转义(例如,如果选择了错误的列名称),请使用[方括号]。

#2

You can use the PIVOT table operator to do so in one query directly instead of these CASE expressions.

您可以使用PIVOT表运算符直接在一个查询中执行此操作,而不是使用这些CASE表达式。

It will do the further step that you are looking for, which is the grouping of records after spreading them using the CASE expression. Something like:

它将执行您正在寻找的进一步步骤,即使用CASE表达式传播它们之后的记录分组。就像是:

SELECT 
  CustId, 
  read, 
  write, 
  own, 
  Destroy
FROM
( 
   SELECT custid, permission, x
   FROM tablename
) AS t
PIVOT
(
   MAX(x)
   FOR permission IN('read', 
                     'write', 
                     'own', 
                     'Destroy')
) AS p;

In your query, if the x is a string literal not an identifier, then you can select it normally as a string literal with an alias in the anchor query SELECT custid, permission, 'x' AS x FROM tablename then the query above should work fine.

在您的查询中,如果x是字符串文字而不是标识符,那么您可以正常选择它作为字符串文字,在锚点查询中使用别名SELECT custid,permission,'x'AS x FROM tablename然后上面的查询应该可以工作精细。

#1