从视图计算点数

Question

>我得到了这个视图(这是示例数据(：

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

Answer 2

试试这个：

SELECT team, sum(points) from (
    select   HomeTeam as team, 
    case 
    when HomeTeamScore > AwayTeamScore then 3
    when HomeTeamScore = AwayTeamScore then 1
    ELSE 0
    end as points
    FROM vW_Match   
    UNION ALL 
    select AwayTeam as team, 
    case 
    when HomeTeamScore > AwayTeamScore then 0
    when HomeTeamScore = AwayTeamScore then 1
    ELSE 3
    end as points 
    FROM vW_Match   
) t
GROUP BY team

Answer 3

使用 CASE 语句比较每支球队的结果，您可以获得每场比赛的相应分数：

select HomeTeam, 
       HomeTeamScore,
       case when HomeTeamScore = AwayTeamScore then 1 
        when HomeTeamScore > AwayTeamScore then 3
        when HomeTeamScore < AwayTeamScore then 0
        end as HomeTeamPoints,
       AwayTeam,
       AwayTeamScore,
       case when AwayTeamScore = HomeTeamScore then 1 
        when AwayTeamScore > HomeTeamScore then 3
        when AwayTeamScore < HomeTeamScore then 0
        end as AwayTeamPoints
from vW_Match

这将为您提供不同列中两个团队的结果。