几个月内按类别划分的玩家排名 [英] Player ranking by categories over months

问题描述

想象下面的Player表，其中包含字段:

Imagine the following Player table, with the fields:

PlayerId, Date, Kills and Gold

我需要在几个月内按类别(技能或金牌)获得球员排名. 这是SELECT:

I need to get the player position by category (kills or gold) over months. This is the SELECT:

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

当我按一个月月进行过滤时，它的效果很好. 问题是@rownumber变量在下个月总是增加+1，这不会带来正确的结果.

It works just fine when I filter it by one month. The problem is the @rownumber variable always increment +1 in the next month, which does not bring the right result.

我并不是要使它具有从后端迭代数月的功能.我该怎么办?

I don't mean to make it a function to iterate over months from backend. How can I do it?

推荐答案

您需要另一个用户变量来告诉您新的月份.

You need another user variable to tell you when it's in a new month.

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

这篇关于几个月内按类别划分的玩家排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！