sql - 如何在查询中添加和划分别名列？

Question

我正在使用 SQL Server 2008。

我正在尝试在一些基本查询中做一些基本的数学运算。我需要把赢、输、总数和百分比加起来。我通常会询问原始数字，然后在将查询返回到页面后进行计算。我想让 SQL Server 有机会更努力地工作。

我想做的是这样的:

SELECT   SUM(case when vote = 1 then 1 else 0 end) as TotalWins,
         SUM(case when vote = 0 then 1 else 0 end) as TotalLosses,
         TotalWins + TotalLosses as TotalPlays,
         TotalPlays / TotalWins  as PctWins

这是我现在正在做的事情:

SELECT   SUM(case when vote = 1 then 1 else 0 end) as TotalWins,
         SUM(case when vote = 0 then 1 else 0 end) as TotalLosses,
         SUM(case when vote = 1 then 1 else 0 end) + SUM(case when vote = 0 then 1 else 0 end) as Votes

在查询中进行这样的简单数学计算的最简单、最简洁的方法是什么？

*编辑: *

虽然我得到了一些很好的答案，但我没有得到我想要的。

我将计算的分数是针对特定团队的，因此，我的结果需要如下所示:

TeamID   Team    Wins   Losses  Totals
1        A's     5      3       8
2        Bee's   7      9       16
3        Seas    1      3       4

SELECT   T.TeamID,
         T.Team,
         V.TotalWins,
         V.TotalLosses,
         V.PctWins
FROM     Teams T 

      JOIN 

     SELECT  V.TeamID,
             SUM(case when vote = 1 then 1 else 0 end) as V.TotWin,
             SUM(case when vote = 0 then 1 else 0 end) as V.TotLoss
     FROM    Votes V
GROUP BY V.TeamID

我尝试了很多东西，但不太知道哪里出了问题。我确信 JOIN 部分是问题所在。我如何将这两个结果集放在一起？

Answer 1

一种方法是将您的查询包装在一个外部查询中:

SELECT TotalWins,
       TotalLosses,
       TotalWins + TotalLosses as TotalPlays,
       TotalPlays / TotalWins  as PctWins
FROM
( SELECT SUM(case when vote = 1 then 1 else 0 end) as TotalWins,
         SUM(case when vote = 0 then 1 else 0 end) as TotalLosses
  FROM ...
)

另一种方法(由 @Mike Christensen 建议)是使用 Common Table Expressions (CTE) :

; WITH Calculation AS 
    ( SELECT SUM(case when vote = 1 then 1 else 0 end) as TotalWins,
             SUM(case when vote = 0 then 1 else 0 end) as TotalLosses
      FROM ...
    )

SELECT TotalWins,
       TotalLosses,
       TotalWins + TotalLosses as TotalPlays,
       TotalPlays / TotalWins  as PctWins
FROM
       Calculation 

旁注:不知道这是否意味着 SQL-Server 中的任何性能差异，但您也可以编写这些总和:

SUM(case when vote = 1 then 1 else 0 end)

作为计数:

COUNT(case when vote = 1 then 1 end)    --- the ELSE NULL is implied