我有如下所示的 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
我能夠以一種非常線性的方式完成這項作業，只需簡單地獲取不同球隊的串列，并為每個球隊回圈遍歷資料并計算獲勝、平局、失敗總數并將其聚合為變數。但是，我想可能有不同的方法來實作這一點。
uj5u.com熱心網友回復：
此處不需要遞回，只需取消主隊和客隊的旋轉，然后按分組進行分組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;
| 團隊 | 勝利 | 抽獎 | 損失 |
|---|---|---|---|
| 博茨瓦納 | 0 | 1 | 0 |
| 剛果民主共和國 | 0 | 1 | 0 |
| 法國 | 1 | 1 | 0 |
| 美國 | 0 | 1 | 1 |
資料庫<>小提琴
uj5u.com熱心網友回復：
通過使用 組合兩個查詢的輸出，有一個更直接的解決方案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
uj5u.com熱心網友回復：
檢查這個，應該在大多數情況下作業。
(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)
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/380115.html
標籤：sql sql-server 查询语句