我在 mysql 中有这个表:
| player1 | player2 | date | fs_1 | fs_2 |
Jack Tom 2015-03-02 10 2
Mark Riddley 2015-05-02 3 1
...
我需要知道在 date_g 报告的比赛之前(例如 10 天前),玩家 1 完成了多少 ace(fs_1)。
这是我尝试但没有成功的方法:
选项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;
提前谢谢。
也许这可以帮助你:
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 用于构建严格的数据集,这些数据集通过明确的 id(谈论 SQL 时的键)相互链接。尊重三个第一范式更容易。这就是为什么您应该使用密钥来识别您的匹配项本身。通过这种方式,您可以使用子查询(子集)来实现您的目标。
然后,请记住 SQL 是STRUCTURED。这是它的力量和弱点,因为您将无法将其用作具有循环和条件的图灵完备编程语言。但是在任何情况下,您都可以为查询找到相同的结构。但是,您可以使用另一种语言与 SQL 查询结果交互,并对结果集本身使用循环和条件。这取决于你。
无论如何,您可能想阅读与 ISO SQL 形式不同的 MySQL GROUP BY 子句:https : //dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句