根据SQL中的匹配结果创建记分卡报告



这是我输入的锦标赛比赛结果的示例,结果列表示第一队还是第二队赢得了比赛(0代表平局(。

score table -
-----------------
t1   t2   result
-----------------
us   aus    1
aus  sa     2
sa   us     0
us   nz     1
Desired output -
---------------------------------
team    played  won lost    draw
---------------------------------
aus     2       0     2     0
us      3       2     0     1
nz      1       0     1     0
sa      2       1     0     1

这是我提出的解决方案,但有可能在不使用UNION运算符的情况下解决它吗?

select 
t1 as team,
count(1) as played,
sum(case when result=1 then 1 else 0 end) as won,
sum(case when result=2 then 1 else 0 end) as lost,
sum(case when result=0 then 1 else 0 end) as draw
from 
(select
t1, 
t2, 
result
from score
union
select
t2, 
t1, 
case when result=1 then 2
when result=2 then 1
else 0 end as result
from score
) t
group by t.t1

您可以通过CROSS APPLY解开数据,然后对结果执行聚合

示例

Select  Team
,played = count(1)
,won    = sum(case when Val=1 then 1 else 0 end)
,lost   = sum(case when Val=2 then 1 else 0 end)
,draw   = sum(case when Val=0 then 1 else 0 end)
From  score A
Cross Apply ( values (T1,Result )
,(T2,case when result=1 then 2 when result=2 then 1 else 0 end)
) B(Team,Val)
Group By Team

结果

team    played  won lost    draw
aus     2       0   2       0
nz      1       0   1       0
sa      2       1   0       1
us      3       2   0       1

相关内容

  • 没有找到相关文章

最新更新