递归查询，用于从一行数据中按不同项获取合计

Question

我有如下SQL数据：

ResultId    HomeTeam    HomeScore   AwayTeam    AwayScore
1045        USA         2           France      3
1046        USA         1           Botswana    1
1047        Botswana    2           France      2

我如何重复这些数据，以获得主客场球队的总胜、平、负？我的输出看起来像：

Team        Wins        Draws      Losses
USA         0           1          1
France      1           1          0
Botswana    0           2          0

我能够以一种非常线性的方式完成这项工作，只需获得一个不同球队的列表，并为每支球队循环查看数据，统计胜、平、负的总数，并将其汇总为变量。然而，我想可能有一种不同的方式来实现这一点。

Answer 1

这里不需要递归，只需取消主边和客场，然后按Team分组并进行条件聚合

SELECT
v.Team,
Wins   = COUNT(CASE WHEN ScoreDiff > 0 THEN 1 END),
Draws  = COUNT(CASE WHEN ScoreDiff = 0 THEN 1 END),
Losses = COUNT(CASE WHEN ScoreDiff < 0 THEN 1 END) 
FROM Matches
CROSS APPLY (VALUES
(HomeTeam, HomeScore - AwayScore),
(AwayTeam, AwayScore - HomeScore)
) v(Team, ScoreDiff)
GROUP BY
v.Team;

获胜损失博茨瓦纳<1>

团队		平局
DRC	0
法国	1	1		0
美国	0	1

有一个更直接的解决方案，通过使用Union All组合两个查询的输出，其中第一个查询处理"的匹配结果；HomeTeam"；，第二个用于"；AwayTeam"；。

Select Team, Sum(Wins), Sum(Draws), Sum(Losses)
From (
Select HomeTeam As Team,
Sum(Iif(HomeScore>AwayScore, 1, 0)) As Wins,
Sum(Iif(HomeScore=AwayScore, 1, 0)) As Draws,
Sum(Iif(HomeScore<AwayScore, 1, 0)) As Losses
From Tbl
Group by HomeTeam
Union All
Select AwayTeam As Team,
Sum(Iif(AwayScore>HomeScore, 1, 0)) As Wins,
Sum(Iif(AwayScore=HomeScore, 1, 0)) As Draws,
Sum(Iif(AwayScore<HomeScore, 1, 0)) As Losses
From Tbl
Group by AwayTeam) As T
Group By Team
Order by Team

检查一下，在大多数情况下应该都能工作。

(select Matches.HomeTeam Team, 
sum(case when a.HomeResult='Win' Then 1 else 0 end) Win, 
sum(case when a.HomeResult='Loss' Then 1 else 0 end) Loss,
sum(case when a.HomeResult='Draw' Then 1 else 0 end) Draw
from 
(select ResultId, 
case
when HomeScore>AwayScore then 'Win'
when HomeScore=AwayScore then 'Draw'
else 'Loss'
end as HomeResult,
case
when HomeScore>AwayScore then 'Loss'
when HomeScore=AwayScore then 'Draw'
else 'Win'
end as AwayResult
from Matches) a join Matches on Matches.ResultId=a.ResultId group by HomeTeam)
Union
(select Matches.AwayTeam Team, 
sum(case when a.AwayResult='Win' Then 1 else 0 end) Win, 
sum(case when a.AwayResult='Loss' Then 1 else 0 end) Loss,
sum(case when a.AwayResult='Draw' Then 1 else 0 end) Draw 
from 
(select ResultId, 
case
when HomeScore>AwayScore then 'Win'
when HomeScore=AwayScore then 'Draw'
else 'Loss'
end as HomeResult,
case
when HomeScore>AwayScore then 'Loss'
when HomeScore=AwayScore then 'Draw'
else 'Win'
end as AwayResult
from Matches) a join Matches on Matches.ResultId=a.ResultId group by AwayTeam)