mysql - 每个玩家每个事件 MySQL 的连胜记录

Question

我已经尝试过使用“连胜”搜索条件的不同解决方案，但我看不出它们适合我的情况。

我有以下表格:

投注历史:

+-------+-------+-----------+
|   id  | betid | betstatus |
+-------+-------+-----------+
| 63281 | 63280 | lose      |
| 63280 | 63279 | won       |
| 63279 | 63278 | lose      |
| 63278 | 63277 | lose      |
| 63276 | 63275 | won       |
| 63275 | 63274 | won       |
| 63271 | 63270 | won       |
| 63270 | 63269 | won       |
| 63269 | 63268 | won       |
| 63268 | 63267 | won       |
| 63267 | 63266 | lose      |
| 63266 | 63265 | won       |
| 63264 | 63263 | won       |
| 63262 | 63261 | won       |
| 63261 | 63260 | lose      |
| 63259 | 63258 | lose      |
| 63258 | 63257 | won       |
| 63257 | 63256 | lose      |
| 63256 | 63255 | lose      |
| 63254 | 63253 | won       |
+-------+-------+-----------+

赌注:

+-------+--------+----------+
| betid | gameid | username |
+-------+--------+----------+
| 63280 |   7506 | player1  |
| 63279 |   7506 | player2  |
| 63278 |   7506 | player5  |
| 63277 |   7506 | player2  |
| 63275 |   7506 | player12 |
| 63274 |   7506 | player12 |
| 63270 |   7506 | player5  |
| 63269 |   7505 | player2  |
| 63268 |   7505 | player12 |
| 63267 |   7505 | player2  |
| 63266 |   7505 | player5  |
| 63265 |   7505 | player1  |
| 63263 |   7505 | player2  |
| 63261 |   7504 | player2  |
| 63260 |   7504 | player5  |
| 63258 |   7504 | player5  |
| 63257 |   7504 | player2  |
| 63256 |   7504 | player2  |
| 63255 |   7503 | player2  |
| 63253 |   7503 | player1  |
+-------+--------+----------+

EventGames:- 另一个存储游戏和事件之间关系的表，可以根据需要轻松集成。

我需要帮助构建 MySQL 语句以确定至少连续五 (5) 次获胜的玩家。

非常感谢。

编辑:下表可能更简单:

+-------+-----------+--------+----------+------+
| id    | betstatus | gameid | username | team |
+-------+-----------+--------+----------+------+
| 63281 | lose      |   7506 | player12 | C    |
| 63280 | won       |   7506 | player7  | A    |
| 63279 | lose      |   7506 | player3  | B    |
| 63278 | lose      |   7506 | player10 | B    |
| 63276 | won       |   7506 | player1  | A    |
| 63275 | won       |   7506 | player1  | A    |
| 63271 | won       |   7506 | player3  | A    |
| 63270 | won       |   7505 | player7  | B    |
| 63269 | won       |   7505 | player1  | B    |
| 63268 | won       |   7505 | player10 | B    |
| 63267 | lose      |   7505 | player3  | A    |
| 63266 | won       |   7505 | player12 | B    |
| 63264 | won       |   7505 | player10 | B    |
| 63262 | won       |   7504 | player10 | B    |
| 63261 | lose      |   7504 | player3  | A    |
| 63259 | lose      |   7504 | player3  | A    |
| 63258 | won       |   7504 | player10 | B    |
| 63257 | lose      |   7504 | player7  | A    |
| 63256 | lose      |   7503 | player7  | A    |
| 63254 | won       |   7503 | player12 | B    |
+-------+-----------+--------+----------+------+

Answer 1

您可以使用变量来获得所需的结果:

SELECT username, MAX(streak) AS streak
FROM (
  SELECT username, betstatus, id,
         @streak := IF(betstatus = 'lose', IF(@uid := username, 0, 0),
                      IF(@uid = username, @streak + 1,
                         IF(@uid := username, 1, 1))) AS streak

  FROM (
    SELECT b.username, bh.betstatus, bh.id
    FROM Bets AS b
    INNER JOIN BetHistory AS bh ON b.betid = bh.betid) AS t 
  CROSS JOIN (SELECT @streak := 0, @uid := '') AS vars
  ORDER BY username, id) AS s
WHERE streak >= 5  
GROUP BY username 
ORDER BY username 

注意:我用的是原来的表结构

注释 2:查询使用嵌套 IF 表达式来正确初始化和访问变量。

Demo here