按周排名组排名

Question

我正试图从我的数据集中获得一个每周排序类型的结果，并且失败了多项技术。

我的数据如下：

+------------+------+--------+
| team       | week |points  |
+------------+------+--------+
| blue       |   1  | 10     | 
| green      |   1  | 15     |
| red        |   1  | 16     |
| blue       |   2  | 5      |
| green      |   2  | 18     |
| red        |   2  | 6      |
| blue       |   3  | 2      |
| green      |   3  | 8      |
| red        |   3  | 8      |
+------------+------+--------+

我试着得到这样的东西：

+------------+------+--------+
| team       | week | rank   | 
+------------+------+--------+
| blue       |   1  | 3      | 
| green      |   1  | 2      |
| red        |   1  | 1      |
| blue       |   2  | 3      |
| green      |   2  | 1      |
| red        |   2  | 2      |
| blue       |   3  | 3      |
| green      |   3  | 1      |
| red        |   3  | 1      |
+------------+------+--------+

你可以看到，在第三周，分数是平的，所以排名也需要平手。打成平手的等级永远是数值中较低的。(即关节2，而不是3)。

它可以是一个存储过程，视图，任何东西。但它必须是MySql。

到目前为止，我有这样的看法：

SELECT team, 
@rnk:= CASE 
    WHEN @week_id <> week THEN 0
    WHEN points = @tot THEN @rnk
    ELSE @rnk+1 
END 
AS rank,
@week_id := week AS wk,
@tot := points AS pts
FROM
  (SELECT @rnk:= -1) s,
  (SELECT @week_id:= -1) c,
  (SELECT *
   FROM v_weeklypointtotals
   ORDER BY week, points DESC
  ) t

但是，当从MySql工作台调用时，它工作得很好。但是当通过PHP调用时，它会给出不正确的结果。(它没有考虑到被打成平手的几周)。我认为这可能与设定变量的顺序有关.但我很困惑。

Answer 1

顺便说一句，如果你不去查看我在评论中链接到的SO帖子，你可以试试这个魔法:)

SELECT    ( 
            CASE Week 
            WHEN @curW 
            THEN @curRow := @curRow + 1 
            ELSE @curRow := 1 AND @curW := Week END
          ) + 1 AS rank,
          Week,
          team,
          points
FROM      tablename,
          (SELECT @curRow := 0, @curW := 0) r
ORDER BY  Week DESC, points DESC;