MYSQL -最近10天的和值

Question

我在mysql中有一张桌子：

|   player1   |  player2 |    date    |   fs_1   |   fs_2   |
    Jack         Tom       2015-03-02       10         2
    Mark        Riddley    2015-05-02       3          1 
     ...

我需要知道有多少aces (fs_1)球员在date_g报道的比赛前(例如10天前)做过多少次比赛。

这就是我尝试过但没有成功的地方：

选项1

    SELECT 
    players_atp.name_p AS 'PLAYER 1',
    P.name_p AS 'PLAYER 2',
    DATE(date_g) AS 'DATE',
    result_g AS 'RESULT',
    FS_1,
    FS_2,
    SUM(IF(date_sub(date_g, interval 10 day)< date_g, FS_1, 0)) AS 'last 10 days'
FROM
    stat_atp stat_atp
        JOIN
    backup3.players_atp ON ID1 = id_P
        JOIN
    backup3.players_atp P ON P.id_p = id2
        JOIN
    backup3.games_atp ON id1_g = id1 AND id2_g = id2
        AND id_t_g = id_t
        AND id_r_g = id_r
WHERE
    date_g > '2015-01-01'
GROUP BY ID1;

选项2

SELECT 
    players_atp.name_p AS 'PLAYER 1',
    P.name_p AS 'PLAYER 2',
    DATE(date_g) AS 'DATE',
    result_g AS 'RESULT',
    FS_1,
    FS_2,
    SUM(CASE WHEN date_g between date_g and date_sub(date_g, interval 10 day) then fs_1 else 0 end) AS 'last 10 days'
FROM
    stat_atp stat_atp
        JOIN
    backup3.players_atp ON ID1 = id_P
        JOIN
    backup3.players_atp P ON P.id_p = id2
        JOIN
    backup3.games_atp ON id1_g = id1 AND id2_g = id2
        AND id_t_g = id_t
        AND id_r_g = id_r
WHERE
    date_g > '2015-01-01'
GROUP BY ID1;

我已经编辑了代码，现在更容易阅读和理解。

 SELECT 
     id1 AS 'PLAYER 1',
     id2 AS 'PLAYER 2',
     DATE(date_g) AS 'DATE',
     result_g AS 'RESULT',
     FS_1,
     FS_2,
     SUM(CASE
         WHEN date_g BETWEEN date_g AND DATE_SUB(date_g, INTERVAL 10 DAY) THEN fs_1
     END) AS 'last 20 days' FROM
     stat_atp stat_atp
         JOIN
     backup3.games_atp ON id1_g = id1 AND id2_g = id2
         AND id_t_g = id_t
         AND id_r_g = id_r GROUP BY ID1;

提前鸣谢。

Answer 1

也许这能帮你：

SELECT 
    id1,
    SUM(fs_1)
FROM 
     stat_atp
WHERE
     date_g <= DATE_SUB('2015-03-02', INTERVAL 1 DAY) AND date_g >= DATE_SUB('2015-03-02', INTERVAL 10 DAY)
AND
    id1='Jack'
GROUP BY id1;

请记住，RDBMS用于构造严格的数据集，这些数据集通过明确的ids (在谈论SQL时使用键)相互链接。尊重三种第一种范式比较容易。这就是为什么你应该使用键来识别你的对手本身。通过这种方式，您可以使用子查询(子集)来实现您的目标。

然后，请记住，SQL是结构化的。这是它的强项和弱点，因为您将无法使用它作为一个具有循环和条件的图灵完整编程语言。但是在任何情况下，您都可以为查询找到相同的结构。但是，您可以与SQL查询结果与另一个语言交互，并在结果集本身上使用循环和条件。那随你的便。

无论如何，您可能希望阅读有关MySQL GROUP BY子句的内容，该子句与ISO表单：https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html不同。