对于一个体育比赛,人们可以两人一组对抗另一个由两人组成的队,我想输出一个特定球员面对的每个对手的列表,以及特定球员和该对手之间比赛的赢、平和输的次数。
例如,如果结果是:
玩家1和玩家2 2-1玩家3和玩家4
玩家3和玩家1 1-0玩家2和玩家4
玩家2和玩家4 4-2玩家1和玩家3
玩家1和玩家4 0-0玩家3和玩家2
我希望玩家1的输出是:
播放器2,W1 D1 L1
播放器3,W1 D1 L0
玩家4,W2 D0 L1
事实证明,要做到这一点非常困难。
我的数据被组织成两个表格,一个是所有球员(球员)的表格,一个是得分(得分)的表格。
玩家有PlayerID和PlayerName两个字段。
分数有Player1、Player2、Player3、Player4和Score字段,其中每个球员字段都可以包含任何PlayerID(因此,根据行的不同,任何给定的ID都可以出现在不同的列中),分数的存储方式是,如果小于21,则Player1和Player2将失败,如果大于21,则获胜。
这个查询有什么问题?很明显它返回了0。

SELECT opponent as opp, COUNT(result='win') as c1, COUNT(result='draw') as c2, COUNT(result='loss') as c3
      FROM (SELECT
            CASE
                WHEN '$player' IN(Players1.PlayerName, Players2.PlayerName) THEN Players3.PlayerName
                WHEN '$player' IN(Players3.PlayerName, Players4.PlayerName) THEN Players1.PlayerName
                ELSE 'NA'
                END as opponent,
            CASE
                WHEN '$player' IN(Players1.PlayerName, Players2.PlayerName)
                THEN
                    CASE
                        WHEN Scores.Score > 21 THEN 'win'
                        ELSE 'NA'
                        END as result,
                    CASE
                        WHEN Scores.Score < 21 THEN 'loss'
                        ELSE 'NA'
                        END as result,
                WHEN '$player' IN(Players3.PlayerName, Players4.PlayerName)
                THEN
                    CASE
                        WHEN Scores.Score < 21 THEN 'win'
                        ELSE 'NA'
                        END as result,
                    CASE
                        WHEN Scores.Score > 21 THEN 'loss'
                        ELSE 'NA'
                        END as result,
                WHEN Scores.Score = 21 THEN 'draw'
                ELSE 'NA'
                END as result,
        FROM Scores
        INNER JOIN Players Players1 ON Scores.Player1=Players1.PlayerID
        INNER JOIN Players Players2 ON Scores.Player2=Players2.PlayerID
        INNER JOIN Players Players3 ON Scores.Player3=Players3.PlayerID
        INNER JOIN Players Players4 ON Scores.Player4=Players4.PlayerID
        WHERE Players1.PlayerName = '$player' OR Players2.PlayerName = '$player' OR Players3.PlayerName = '$player' OR Players4.PlayerName = '$player'
        UNION ALL
        SELECT
            CASE
                WHEN '$player' IN(Players1.PlayerName, Players2.PlayerName) THEN Players4.PlayerName
                WHEN '$player' IN(Players3.PlayerName, Players4.PlayerName) THEN Players2.PlayerName
                ELSE 'NA'
                END as opponent,
            CASE
                WHEN '$player' IN(Players1.PlayerName, Players2.PlayerName) AND Scores.Score > 21 THEN 'win'
                WHEN '$player' IN(Players1.PlayerName, Players2.PlayerName) AND Scores.Score < 21 THEN 'loss'
                WHEN '$player' IN(Players3.PlayerName, Players4.PlayerName) AND Scores.Score < 21 THEN 'win'
                WHEN '$player' IN(Players3.PlayerName, Players4.PlayerName) AND Scores.Score > 21 THEN 'loss'
                WHEN Scores.Score = 21 THEN 'draw'
                ELSE 'NA'
                END as result,
        FROM Scores
        INNER JOIN Players Players1 ON Scores.Player1=Players1.PlayerID
        INNER JOIN Players Players2 ON Scores.Player2=Players2.PlayerID
        INNER JOIN Players Players3 ON Scores.Player3=Players3.PlayerID
        INNER JOIN Players Players4 ON Scores.Player4=Players4.PlayerID
        WHERE Players1.PlayerName = '$player' OR Players2.PlayerName = '$player' OR Players3.PlayerName = '$player' OR Players4.PlayerName = '$player'
    ) AS SUBQUERY
    GROUP BY opp

最佳答案

这并不漂亮,但是由于MySQL缺少Pivot函数,您可以使用一个内部查询来分析结果,该查询可以分解谁玩的以及结果是什么。然后外部查询可以聚合这些结果。下面这些对我很管用,不过,同样,也不好看:

select players.name as playerName,
  opponents.name as opponentName,
  SUM(CASE WHEN result = 'W'THEN 1 ELSE 0 END) AS win,
  SUM(CASE WHEN result = 'L'THEN 1 ELSE 0 END) AS lose,
  SUM(CASE WHEN result = 'D' THEN 1 ELSE 0 END) AS draw
from  (
    select player1 as player, player3 as opponent, score, CASE WHEN score > 21 THEN 'W' WHEN score < 21 THEN 'L' ELSE 'D' END as result
    from scores
    union all
    select player1 as player, player4 as opponent, score, CASE WHEN score > 21 THEN 'W' WHEN score < 21 THEN 'L' ELSE 'D' END as result
    from scores
    union all
    select player2 as player, player3 as opponent, score, CASE WHEN score > 21 THEN 'W' WHEN score < 21 THEN 'L' ELSE 'D' END as result
    from scores
    union all
    select player2 as player, player4 as opponent, score, CASE WHEN score > 21 THEN 'W' WHEN score < 21 THEN 'L' ELSE 'D' END as result
    from scores
    -- now reverse result because opponents are being slotted into the player position
    union all
    select player3 as player, player1 as opponent, score, CASE WHEN score > 21 THEN 'L' WHEN score < 21 THEN 'W' ELSE 'D' END as result
    from scores
    union all
    select player4 as player, player1 as opponent, score, CASE WHEN score > 21 THEN 'L' WHEN score < 21 THEN 'W' ELSE 'D' END as result
    from scores
    union all
    select player3 as player, player2 as opponent, score, CASE WHEN score > 21 THEN 'L' WHEN score < 21 THEN 'W' ELSE 'D' END as result
    from scores
    union all
    select player4 as player, player2 as opponent, score, CASE WHEN score > 21 THEN 'L' WHEN score < 21 THEN 'W' ELSE 'D' END as result
    from scores
) resultsByPlayer
inner join players on players.playerId = resultsByPlayer.player
inner join players as opponents on opponents.playerId = resultsByPlayer.opponent
group by players.playerId, opponents.playerId

10-06 06:18