mysql - 如何从另一个表的列中对表进行排序然后运行查询？

Question

我正在为我们的球队建立一个网站，以获取乐趣，并使用 PHP 和 SQL 作为数据库来跟踪统计数据。我是通过阅读手册和论坛来学习的。我正在构建一个查询，该查询将显示当前最长的连续命中数。我偶然发现了一个关于检测运行和条纹的页面，并正在尝试使用它。我对所有这些东西都很陌生，所以也许我的表格结构不正确。

表“游戏”

+--------+------------+------+
| GameID |    Date    | Time |
+--------+------------+------+
|      1 | 2015/08/19 | 6:30 |
|      2 | 2015/08/20 | 6:30 |
|      3 | 2015/08/22 | 6:30 |
|      4 | 2015/08/24 | 8:00 |
|      5 | 2015/08/24 | 6:30 |
|      6 | 2015/07/15 | 8:00 |
+--------+------------+------+

表“玩家”

+--------+----+---+
| GameID | AB | H |
+--------+----+---+
|      1 |  3 | 1 |
|      2 |  4 | 2 |
|      3 |  2 | 0 |
|      4 |  3 | 0 |
|      5 |  2 | 1 |
|      6 |  3 | 0 |
+--------+----+---+

代码

SELECT games.GameID, GR.H, 
    (SELECT COUNT(*) 
    FROM player G 
    WHERE (CASE WHEN G.H > 0 THEN 1 ELSE 0 END) <> (CASE WHEN GR.H > 0 THEN 1 ELSE 0 END) 
    AND G.GameID <= GR.GameID) as RunGroup 
FROM player GR
INNER JOIN games
ON GR.gameID = games.GameID
ORDER BY Date ASC, Time ASC

基本上，为了正确获得连续命中，我需要在执行 RunGroup 部分之前根据“games”表上的日期 (ASC) 和时间 (ASC) 对“player”表上的 GameID 重新排序代码。显然，通过添加 ORDER BY，只有在 RunGroup 完成查询并导致数据不正确后，所有内容才会排序。我已经被困在这里几天了，现在需要一些帮助。

我目前得到的结果是:

+--------+---+----------+
| GameID | H | RunGroup |
+--------+---+----------+
|      6 | 0 |        3 |
|      1 | 1 |        0 |
|      2 | 2 |        0 |
|      3 | 0 |        2 |
|      5 | 1 |        2 |
|      4 | 0 |        2 |
+--------+---+----------+

这就是我想要实现的目标:

+--------+---+----------+
| GameID | H | RunGroup |
+--------+---+----------+
|      6 | 0 |        0 |
|      1 | 1 |        1 |
|      2 | 2 |        1 |
|      3 | 0 |        2 |
|      5 | 1 |        2 |
|      4 | 0 |        3 |
+--------+---+----------+

谢谢

Answer 1

考虑以下因素:

DROP TABLE IF EXISTS games;

CREATE TABLE games
(game_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,date_played DATETIME NOT NULL
);

INSERT INTO games VALUES
(1,'2015/08/19 18:30:00'),
(2,'2015/08/20 18:30:00'),
(3,'2015/08/22 18:30:00'),
(4,'2015/08/24 20:00:00'),
(5,'2015/08/24 18:30:00'),
(6,'2015/07/15 20:00:00');

DROP TABLE IF EXISTS stats;

CREATE TABLE stats
(player_id INT NOT NULL
,game_id INT NOT NULL
,at_bat INT NOT NULL
,hits INT NOT NULL
,PRIMARY KEY(player_id,game_id)
);

INSERT INTO stats VALUES
(1,1,3,1),
(1,2,4,2),
(1,3,2,0),
(1,4,3,0),
(1,5,2,1),
(1,6,3,0),
(2,1,2,1),
(2,2,3,2),
(2,3,3,0),
(2,4,3,1),
(2,5,2,1),
(2,6,3,0);

SELECT x.*
     , SUM(y.at_bat) runningAB
     , SUM(y.hits) runningH 
     , SUM(y.hits)/SUM(y.at_bat) BA   
  FROM 
     (
       SELECT s.*, g.date_played FROM stats s JOIN games g ON g.game_id = s.game_id
     ) x
  JOIN
     (
       SELECT s.*, g.date_played FROM stats s JOIN games g ON g.game_id = s.game_id
     ) y
    ON y.player_id = x.player_id 
   AND y.date_played <= x.date_played
 GROUP 
    BY x.player_id
     , x.date_played;

+-----------+---------+--------+------+---------------------+-----------+----------+--------+
| player_id | game_id | at_bat | hits | date_played         | runningAB | runningH | BA     |
+-----------+---------+--------+------+---------------------+-----------+----------+--------+
|         1 |       6 |      3 |    0 | 2015-07-15 20:00:00 |         3 |        0 | 0.0000 |
|         1 |       1 |      3 |    1 | 2015-08-19 18:30:00 |         6 |        1 | 0.1667 |
|         1 |       2 |      4 |    2 | 2015-08-20 18:30:00 |        10 |        3 | 0.3000 |
|         1 |       3 |      2 |    0 | 2015-08-22 18:30:00 |        12 |        3 | 0.2500 |
|         1 |       5 |      2 |    1 | 2015-08-24 18:30:00 |        14 |        4 | 0.2857 |
|         1 |       4 |      3 |    0 | 2015-08-24 20:00:00 |        17 |        4 | 0.2353 |
|         2 |       6 |      3 |    0 | 2015-07-15 20:00:00 |         3 |        0 | 0.0000 |
|         2 |       1 |      2 |    1 | 2015-08-19 18:30:00 |         5 |        1 | 0.2000 |
|         2 |       2 |      3 |    2 | 2015-08-20 18:30:00 |         8 |        3 | 0.3750 |
|         2 |       3 |      3 |    0 | 2015-08-22 18:30:00 |        11 |        3 | 0.2727 |
|         2 |       5 |      2 |    1 | 2015-08-24 18:30:00 |        13 |        4 | 0.3077 |
|         2 |       4 |      3 |    1 | 2015-08-24 20:00:00 |        16 |        5 | 0.3125 |
+-----------+---------+--------+------+---------------------+-----------+----------+--------+