MYSQL查询连胜包括比分

Question

我有一个查询生成的表，只要玩家不断获胜，它就会计算连胜次数。当玩家获得正分时，连胜数上升 1，如果获得负分，则连胜数回落​​至 0。表格如下:

+--------+------------------+--------+--------+
| player |    timestamp     | points | streak |
+--------+------------------+--------+--------+
| John   | 22/11/2012 23:01 |     -2 |      0 |
| John   | 22/11/2012 23:02 |      3 |      1 |
| John   | 22/11/2012 23:04 |      5 |      2 |
| John   | 22/11/2012 23:05 |     -2 |      0 |
| John   | 22/11/2012 23:18 |     15 |      1 |
| John   | 23/11/2012 23:20 |      5 |      2 |
| Chris  | 27/11/2012 22:12 |     20 |      1 |
| Chris  | 27/11/2012 22:14 |    -12 |      0 |
| Chris  | 27/11/2012 22:17 |      4 |      1 |
| Chris  | 27/11/2012 22:18 |     -4 |      0 |
| Chris  | 27/11/2012 22:20 |     10 |      1 |
| Chris  | 27/11/2012 22:21 |     20 |      2 |
| Chris  | 27/11/2012 22:22 |     90 |      3 |
+--------+------------------+--------+--------+

我想获得球员的最大连胜数，这当然很容易获得，但我也想包括球员在该特定连胜数中获得的分数。因此，对于上面的示例，结果必须如下所示:

+--------+--------+-----------+
| player | points | maxstreak |
+--------+--------+-----------+
| John   |     20 |         2 |
| Chris  |    120 |         3 |
+--------+--------+-----------+

有什么想法可以实现这一目标吗？提前致谢!

Answer 1

我还没有机会实际尝试这个，但应该使用 mySQL 变量...

一开始，最里面的查询只是从分数表中查询，并强制按照玩家和时间戳的顺序获取数据。由此，我必须按顺序处理 MySQL 变量。首先...在处理每条新记录时，如果我在不同的“玩家”上(实际上应该基于 ID 而不是名称)，我会将 streak、points、maxStreak、maxStreakPoints 重置为零，然后将最后一个用户设置为要处理的用户。

此后，我立即检查连胜状态、积分等...

将所有内容制成表格后，我会使用 OUTERMOST 查询来获取每个玩家的最高最大连续得分/最大连续得分。

SELECT
      Final.Player,
      MAX( Final.MaxStreak ) MaxStreak,
      MAX( Final.MaxStreakPoints ) MaxStreakPoints 
   FROM 
      ( 
        SELECT 
              PreOrd.Player,
              PreOrd.TimeStamp,
              PreOrd.Points,
              @nStreak       := case when PreOrd.Points < 0 then 0
                                     when PreOrd.Player = @cLastPlayer then @nStreak +1
                                     else 1 end  Streak,                              

              @nStreakPoints := case when @nStreak = 1 then PreOrd.Points
                                     when @nStreak > 1 then @nStreakPoints + PreOrd.Points
                                     else 0 end  StreakPoints,

              @nMaxStreak    := case when PreOrd.Player != @cLastPlayer then @nStreak
                                     when @nStreak > @nMaxStreak then @nStreak
                                     else @nMaxStreak end MaxStreak,

              @nMaxStreakPoints := case when PreOrd.Player != @cLastPlayer then @nStreakPoints
                                        when @nStreak >= @nMaxStreak and @nStreakPoints > @nMaxStreakPoints then @nStreakPoints
                                        else @nMaxStreakPoints end MaxStreakPoints,

              @cLastPlayer := PreOrd.Player PlayerChange
         FROM 
              ( select
                      S.Player,
                      S.TimeStamp,
                      S.Points
                   from 
                      Scores2 S
                   ORDER BY 
                      S.Player,
                      S.TimeStamp,
                      S.`index` ) PreOrd,
              ( select
                      @nStreak := 0,
                      @nStreakPoints := 0,
                      @nMaxStreak := 0,
                      @nMaxStreakPoints := 0,
                      @cLastPlayer := '~' ) SQLVars
      ) as Final
   group by 
      Final.Player 

现在，这可能会给出错误的最大连续分数，例如，在单个分数上，该人有 90 分，然后连续 1 分 10 分，2 分 10 分，3 分 10 分，总共 30 分。不过想想……:)

这是当我添加 index 列时得到的结果，正如您从提供的数据中提供的那样

SQL Fiddle Showing my solution...