sql - 在列上执行数学的更优雅的方式

Question

有没有更优雅的方法:

SELECT TOP (@NumOfGames) 
        (SUM(IIF(Fixture.HomeTeamID = @Team
            ,IIF(Fixture.Goals.FullTimeHomeGoals > Fixture.Goals.FullTimeAwayGoals
                    ,1 
                    ,IIF(Fixture.Goals.FullTimeHomeGoals < Fixture.Goals.FullTimeAwayGoals, 0, 0.5) 
                ) --IsHomeTeam
            ,IIF(Fixture.Goals.FullTimeAwayGoals > Fixture.Goals.FullTimeHomeGoals
                    ,1 
                    ,IIF(Fixture.Goals.FullTimeAwayGoals < Fixture.Goals.FullTimeHomeGoals, 0, 0.5) 
                ) --IsAwayTeam
        )) / @NumOfGames) * 100 AS Result
    FROM 
        Fixture.Fixture 
    INNER JOIN 
        Fixture.Goals ON Fixture.Goals.FixtureID  = Fixture.Fixture.FixtureID
    WHERE 
        HomeTeamID = @Team 
        OR 
        AwayTeamID = @Team 

我非常讨厌 SUM 部分，一定有更好的方法来做到这一点。

用户将@NumOfGames 和@Team 作为参数输入到存储过程。然后它会检查目标表以查看球队是否赢、平或输。赢是1分，平是0.5，输是0。我想输出这些数字的总和，除以NumOfGames再乘以100得到成功率。

Answer 1

哎呀。 Microsoft 似乎已将 IIF 添加到 SQL Server 2012 中。无论如何，您可能会发现标准 SQL 方式更具吸引力。这将使用 CASE 语句:

SELECT TOP (@NumOfGames) 
       (SUM(case when Fixture.HomeTeamID = @Team and
                      Fixture.Goals.FullTimeHomeGoals > Fixture.Goals.FullTimeAwayGoals
                 then 1.0
                 when Fixture.HomeTeamID = @Team and
                      Fixture.Goals.FullTimeHomeGoals = Fixture.Goals.FullTimeAwayGoals
                 then 0.5
                 when Fixture.HomeTeamID = @Team
                 then 0.0
                 when Fixture.HomeTeamID <> @Team and
                      Fixture.Goals.FullTimeAwayGoals > Fixture.Goals.FullTimeHomeGoals
                 then 1.0
                 when Fixture.HomeTeamID <> @Team and
                      Fixture.Goals.FullTimeAwayGoals = Fixture.Goals.FullTimeHomeGoals
                 then 0.5
                 else 0.0
        end) / @NumOfGames) * 100 AS Result
FROM 
    Fixture.Fixture 
INNER JOIN 
    Fixture.Goals ON Fixture.Goals.FixtureID  = Fixture.Fixture.FixtureID
WHERE 
    HomeTeamID = @Team 
    OR 
    AwayTeamID = @Team 

作为记录，比较“Fixture.HomeTeamID <> @Team”在 case 语句中是不必要的，只是为了使其更具可读性(并且效率降低一点)。 case 语句的工作原理是按顺序评估 WHEN 子句，然后选择第一个匹配的子句。

我们可以请求微软删除 IIF() 吗？ ;-)