mySQL - 连胜纪录

Question

您好，我正在尝试找出一种方法来为我的表格中的每个成员找到最大的连胜记录。 build table 时，这从未在计划中发生，所以我寻求帮助以实现这一目标。

我的结构如下:

id  player_id   opponant_id     won     loss    timestamp 

如果是个人游戏，玩家id就是他们的id。如果他们被某人挑战，他们的 id 就是对手的 id，而输赢(1 或 0)与 player_id 相关。

我想找出每个用户的最大连胜记录。

任何人对如何使用当前表结构执行此操作有任何想法。

问候

编辑

这里是一些测试数据，其中 id 3 是有问题的玩家:

id  player_id   won     loss    timestamp
1   6           0       1       2012-03-14 13:31:00
13  3           0       1       2012-03-15 13:10:40
17  3           0       1       2012-03-15 13:29:56
19  4           0       1       2012-03-15 13:37:36
51  3           1       0       2012-03-16 13:20:05
53  6           0       1       2012-03-16 13:32:38
81  3           0       1       2012-03-21 13:14:49
89  4           1       0       2012-03-21 14:01:28
91  5           0       1       2012-03-22 13:14:20

Answer 1

试一试。 编辑以考虑损失行

SELECT 
    d.player_id,
    MAX(d.winStreak) AS maxWinStreak
FROM (
    SELECT
        @cUser := 0,
        @winStreak := 0
) v, (

    SELECT
        player_id,
        won,
        timestamp,
        @winStreak := IF(won=1,IF(@cUser=player_id,@winStreak+1,1),0) AS winStreak,
        @cUser := player_id
    FROM (
        (
            -- Get results where player == player_id
            SELECT
                player_id,
                won,
                timestamp
            FROM matchTable
        ) UNION (
            -- Get results where player == opponent_id (loss=1 is good)
            SELECT
                opponent_id,
                loss,
                timestamp
            FROM matchtable
        )
    ) m
    ORDER BY 
        player_id ASC,
        timestamp ASC
) d
GROUP BY d.player_id

这通过选择所有赢/输并计算连胜来实现。然后，子查询按 player_id 分组，循环时计算的最大 winStreak 是每个玩家的输出。

无论如何，它似乎对我的测试数据集都能很好地工作:)

为了更有效地做到这一点，我会重组，即

matches (
    matchID,
    winningPlayerID,
    timeStamp
)

players (
    playerID
    -- player name etc
)

matchesHasPlayers (
    matchID,
    playerID
)

这将导致内部查询

SELECT
    matches.matchID,
    matchesHasPlayers.playerID,
    IF(matches.winningPlayerID=matchesHasPlayers.playerID,1,0) AS won
    matches.timestamp
FROM matches
INNER JOIN matchesHasPlayers
ORDER BY matches.timestamp

导致

SELECT 
    d.player_id,
    MAX(d.winStreak) AS maxWinStreak
FROM (
    SELECT
        @cUser := 0,
        @winStreak := 0
) v, (
    SELECT
        matchesHasPlayers.playerID,
        matches.timestamp,
        @winStreak := IF(matches.winningPlayerID=matchesHasPlayers.playerID,IF(@cUser=matchesHasPlayers.playerID,@winStreak+1,1),0) AS winStreak,
        @cUser := matchesHasPlayers.playerID
    FROM matches
    INNER JOIN matchesHasPlayers
    ORDER BY 
        matchesHasPlayers.playerID ASC,
        matches.timestamp ASC
) d
GROUP BY d.player_id