sql-server - 从 View 中计算点数

Question

我得到了这个 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分？

Answer 1

你可以试试

  ;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