想象一下下面的情况Player
表,包含字段:
PlayerId, Date, Kills and Gold
我需要在几个月内按类别(击杀或金牌)获取玩家位置。
这是选择:
SET @rownumber := 0;
SELECT date, rank, kills FROM (
SELECT pla.event_date, @rownumber := @rownumber + 1 AS rank,
pla.kills, pla.player_id
FROM player AS pla
INNER JOIN ...
WHERE.pla.event_date >= '2017-09-01' AND pla.event_date <= '2017-12-31'
ORDER BY pla.kills DESC
) AS result WHERE player_id = 651894
当我过滤它时它工作得很好one月。
问题是@rownumber
变量总是在下个月增加+1,这不会带来正确的结果。
我并不是想让它成为一个从后端迭代数月的函数。我该怎么做?
您需要另一个用户变量来告诉您新月份的时间。
SET @rownumber := 0;
SET @month := 0;
SELECT pla.event_date,
@rownumber := IF(@month=MONTH(pla.event_date), @rownumber + 1, 1) AS rank,
@month := MONTH(pla.event_date) AS month,
pla.kills, pla.player_id
FROM player AS pla
INNER JOIN ...
WHERE pla.event_date >= '2017-09-01' AND pla.event_date <= '2017-12-31'
ORDER BY MONTH(pla.event_date), pla.kills DESC
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)