sql-server - 从 View 计数点

我得到了这个视图（这是示例数据）：

HomeTeam          HomeTeamScore        AwayTeamScore       AwayTeam
---------------------------------------------------------------
Middlesbrough         3                       1            Manchester United
Manchester City       1                       1            Liverpool
Liverpool             2                       0            Middlesbrough
Manchester United     3                       2            Manchester City

我用这种方法计算主队的进球数（类似地，我计算客队的进球数）：

SELECT HomeTeam, SUM (HomeTeamScore) AS CountGoals
FROM vW_Match
GROUP BY HomeTeam ORDER BY CountGoals DESC

我如何计算队伍的分数？如果结果是两队各得1分，如果一队得分比客队多，就得3分，如果客队得分多，就得3分？

最佳答案

你可以试试

  ;WITH temps AS
  (SELECT HomeTeam AS Team,
          case when HomeTeamScore = AwayTeamScore then 1
               when HomeTeamScore > AwayTeamScore then 3
                 ELSE 0
          end AS Point
   FROM vW_Match
   UNION ALL
   SELECT AwayTeam AS Team,
          case when HomeTeamScore = AwayTeamScore then 1
               when AwayTeamScore  > HomeTeamScore  then 3
               ELSE 0
          end AS Point
   FROM vW_Match
   )
  select  t.Team, sum(t.Point) as TotalPoint
  from temps t
  group by t.Team

关于sql-server - 从 View 计数点，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42997115/