sql-server-2008 - 在同一个选择中使用别名

Question

一旦我设置了这样的列名 SUM(someColumn) AS [ColumnName] ，我以后如何使用它？

假设我构建了一个这样的查询:

SELECT SUM(foo.A + foo.B + foo.C + foo.D + foo.E) AS [TotalFoo],
       SUM(bar.A + bar.B + bar.C + bar.D + bar.E) AS [TotalBar],
       -- I need [TotalFooBar]

在同一个选择中，我想要总数 [TotalFoo]和 [TotalBar] .是否可以引用这些列？我可以做到很长的路要走:

SELECT SUM(foo.A + foo.B + foo.C + foo.D + foo.E) AS [TotalFoo],
       SUM(bar.A + bar.B + bar.C + bar.D + bar.E) AS [TotalBar],
       SUM((foo.A + foo.B + foo.C + foo.D + foo.E) +
           (bar.A + bar.B + bar.C + bar.D + bar.E)) AS [TotalFooBar]

在这个例子中没问题，但我有 更多列，很难遵循。然后如果我必须使用这个列 ([TotalFooBar]) 怎么办？我每次都必须重写整个 SUM 吗？
我正在寻找的是这样的:

SELECT SUM(foo.A + foo.B + foo.C + foo.D + foo.E) AS [TotalFoo],
       SUM(bar.A + bar.B + bar.C + bar.D + bar.E) AS [TotalBar],
       SUM([TotalFoo] + [TotalBar]) AS [TotalFooBar]

甚至有可能吗？

Answer 1

否 ，您不能在同一个 SELECT 中执行此操作语句，但您可以使用子查询:

SELECT 
  TotalFoo,
  TotalBar,
  TotalFoo + TotalBar AS TotalFooBar
FROM
(
    SELECT SUM(foo.A + foo.B + foo.C + foo.D + foo.E) AS [TotalFoo],
           SUM(bar.A + bar.B + bar.C + bar.D + bar.E) AS [TotalBar],
           ...
    FROM ...
) AS sub
...

或 CTE:

WITH CTE
AS
(
   SELECT SUM(foo.A + foo.B + foo.C + foo.D + foo.E) AS [TotalFoo],
          SUM(bar.A + bar.B + bar.C + bar.D + bar.E) AS [TotalBar],
          ...
   FROM ...
)
SELECT 
  TotalFoo,
  TotalBar,
  TotalFoo + TotalBar AS TotalFooBar
FROM CTE