这是我的问题。我有一个包含以下列的表格:
player_id | win_amount | loss_amount | datetime
每条记录都包含玩家的 id、输赢金额(另一个字段变为 0)和日期时间。我想要一个 MySQL 查询来获得以下信息:
player_id
sum(win_amount) - sum(loss_amount)
那个球员的loss_amount
loss_amount
以前的日期时间尝试以下查询,利用GROUP BY
一些聚合函数和相关子查询。替换table
为您的实际表名:
SELECT
t1.player_id,
SUM(t1.win_amount) - SUM(t1.loss_amount) AS net_win_amount,
MAX(IF(t1.loss_amount > 0,
t1.datetime,
NULL)) AS last_loss_datetime,
(SELECT
t2.loss_amount
FROM
`table` AS t2
WHERE
t2.player_id = t1.player_id
AND t2.loss_amount > 0
ORDER BY t2.datetime DESC
LIMIT 0 , 1) AS last_loss_amount
FROM
`table` AS t1
GROUP BY t1.player_id
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句