代码之家 › 专栏 › 技术社区 › Felix

将两行的集合透视到一列中

pivot sqlite database sql

2

Felix · 技术社区 · 7 年前

我有一个团队间比赛的数据库,结构如下(简化):

我想知道迄今为止所有比赛的比分。我可以通过以下方式获得比赛分数:

SELECT match.home, match.away, player.team, COUNT(*) FROM match
    JOIN goal ON match.id = goal.match
    JOIN player ON goal.scorer = player.id
    
    WHERE match.id = 1 GROUP BY player.team;  -- Specific match
    GROUP BY match.id, player.team;           -- All matches

Home | Away | Team  | Count(*)
-----+------+-------+---------
T1   | T2   | T1    | 3
T1   | T2   | T2    | 1
T3   | T1   | T3    | 0
T3   | T1   | T1    | 2

但有两个问题:如果一支或两支球队都没有得分,表格中就不会显示球队名称和零。我试图将玩家加入到目标中,但结果发现SQLite中不支持它们。但这是一个较小的问题。

我想在以下表格中获取有关迄今为止玩过的所有游戏的信息:

MatchID | Home | Away | HomeScore | AwayScore
--------+------+------+-----------+----------
1       | T1   | T2   | 3         | 1
2       | T3   | T1   | 0         | 2

我相信 this question 或 this one 可能是答案,但我不确定如何开始应用到我的案例中。我知道,使用Python或其他外部工具可以实现这一点,但我正在寻找SQL解决方案。任何帮助都将不胜感激!

小提琴连杆

这里有一个指向SQL FIDLE的链接: Fiddle me this

1 回复 | 直到 5 年前

1

0

Parfait 7 年前

考虑加入家聚合结果集和离开聚合结果集每个左连接到火柴返回所有比赛,包括未得分的比赛。

SELECT m.MatchID, m.Home, m.Away, 
       IFNULL(h.Score, 0) AS HomeScore, IFNULL(a.Score, 0) AS AwayScore
FROM 
    Match m
LEFT JOIN
   (SELECT m.MatchID, m.Home, Count(*) As Score 
    FROM Match m
    LEFT JOIN Goal g ON m.MatchID = g.Match 
    INNER JOIN Player p ON g.Player = p.PlayerID AND p.Team = m.Home
   GROUP BY m.MatchID, m.Home) As h 

ON m.MatchID = h.MatchID

LEFT JOIN
   (SELECT m.MatchID, m.Away, Count(*) As Score 
    FROM Team t
    INNER JOIN Match m ON t.Name = m.Away
    LEFT JOIN Goal g ON m.MatchID = g.Match 
    INNER JOIN Player p ON g.Player = p.PlayerID AND p.Team = t.Name
   GROUP BY m.MatchID, m.Away) As a

ON m.MatchID = a.MatchID

SQL Fiddle Demo