我想出了这个查询,它返回了我需要的数据 - 建议我使用 UNION 和可能的 PIVOT,但 SQL 技能有限,这就是我想出的。我确实尝试了 PIVOT,但得到了相同的结果
SELECT RoundNo, Team, WL
FROM `MatchDetails2017`
WHERE Season = 2017 AND Team = 'Team A'
UNION
SELECT RoundNo, Team, WL
FROM `MatchDetails2017`
WHERE Season = 2017 AND Team = 'Team B'
数据是这样的
RoundNo | Team |WL
R1 | Team A | W
R2 | Team A | L
R3 | Team A | W
R4 | Team A | W
R1 | Team B | L
R2 | Team B | W
R3 | Team B | L
R4 | Team B | L
但我想把这样的结果放
出来RoundNo | Team A | Team B
R1 | W | L
R2 | L | W
R3 | W | L
R4 | W | L
我已经按照建议更新了查询,但它在显示为
RoundNo | Team | Team A | Team B
R1 | Team A | W | 0
R1 | Team B | 0 | L
R2 | Team A | L | 0
R2 | Team B | 0 | W
R3 | Team A | W | 0
R3 | Team B | 0 | L
R4 | Team A | W | 0
R4 | Team B | 0 | L
我明白了 - 我刚刚从打开的 SELECT 中删除了团队和分组依据
SELECT Season, RoundNo,
MAX(CASE WHEN Team = 'Team A' THEN WL ELSE 0 END) as Team_A,
MAX(CASE WHEN Team = 'Team B' THEN WL ELSE 0 END) as Team_B
FROM `MatchDetails2017`
WHERE Season = 2017
GROUP BY RoundNo