mysql - 在 MySQL 中排序排行榜

Question

我使用以下 SQL 在我的排行榜表中按排名排序分数:

SELECT score, 1+(SELECT COUNT(*) FROM leaderboard a WHERE a.score > b.score) AS rank 
FROM leaderboard b 
WHERE stage=1 
GROUP BY id

我的表架构是这样的:

CREATE TABLE `leaderboard` (
  `auto_id` int(11) NOT NULL AUTO_INCREMENT,
  `score` int(11) NOT NULL DEFAULT '0',
  `id` int(11) NOT NULL,
  `created_on` datetime NOT NULL,
  PRIMARY KEY (`auto_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

一些示例数据行如下:

auto_id     score      id  created_on
====================================================
1,          72023456,   1, '2014-12-30 11:49:59'
2,          1420234,    1, '2014-12-30 12:00:21'
3,          420234,     1, '2014-12-30 12:00:38'
4,          16382,      1, '2014-12-30 16:31:12'
5,          16382,      1, '2014-12-30 16:34:18'
6,          16382,      1, '2014-12-30 16:37:43'
7,          17713,      1, '2014-12-30 16:38:35'
8,          17257,      1, '2014-12-30 18:53:45'
9,          10625,      1, '2014-12-30 18:58:10'
10,         17272,      1, '2014-12-30 18:58:59'
11,         17328,      1, '2014-12-30 18:59:44'
12,         17267,      37, '2015-01-02 17:11:59'
13,         16267,      37, '2015-01-02 17:12:30'
14,         16267,      37, '2015-01-02 17:13:02'
15,         35509,      37, '2015-01-02 17:17:46'
16,         18286,      37, '2015-01-02 18:20:09'
17,         16279,      37, '2015-01-02 18:20:43'
18,         16264,      37, '2015-01-02 18:21:15'
19,         16265,      37, '2015-01-02 18:40:04'

因为 id 是玩家的 ID，我必须 GROUP BY id。结果如下:

id   score        rank
=========================
1    72023456     1
37   17267        11

如何获得如下预期结果？

id   score        rank
=========================
1    72023456     1
37   35509        2

目前的问题是，现有结果不是玩家的MAX分数。

奖励:我的最终目标是让条目比特定 id 高 1 级和低 1 级。

Answer 1

由于 MySql 没有窗口函数，您需要的查询必须模仿其行为，因此您必须使用变量。

select id, score, @rank :=@rank+1 as rank
  from (
         SELECT b.id, max(b.score) as score
           FROM leaderboard b
          GROUP BY id
          order by score desc
        ) tab
        ,(select @rank := 0) r

编辑:我犯了一个小错误。我现在已经更正了。

输出将是:

id   score        rank
=========================
1    72023456     1
37   35509        2

基本上我正在做的是在查询上创建一个迭代器，它会为每一行递增变量。当我添加顺序时，它将根据该顺序对您的值进行排名。但是该排名必须在查询之外发生，因为如果有两个以上的 ID，则与排名一起的顺序会把事情搞砸

我将使用“1 rank higher & 1 rank lower than specific id.”的解决方案编辑查询。

编辑:为了奖金(虽然不漂亮)

select id, score, rank
from (
   select tab.id, tab.score, @rank :=@rank+1 as rank
     from (select @rank := 0) r,
            (SELECT b.id, max(b.score) as score
            FROM leaderboard b
            GROUP BY id
            order by score desc) tab
) spec
where spec.id=2
UNION
select id, score, rank
from (
   select tab.id, tab.score, @rank :=@rank+1 as rank
     from (select @rank := 0) r,
            (SELECT b.id, max(b.score) as score
            FROM leaderboard b
            GROUP BY id
            order by score desc) tab
) spec
where spec.rank=
(select rank-1
from (
   select tab.id, tab.score, @rank :=@rank+1 as rank
     from (select @rank := 0) r,
            (SELECT b.id, max(b.score) as score
            FROM leaderboard b
            GROUP BY id
            order by score desc) tab
) spec
where spec.id=2)
UNION
select id, score, rank
from (
   select tab.id, tab.score, @rank :=@rank+1 as rank
     from (select @rank := 0) r,
            (SELECT b.id, max(b.score) as score
            FROM leaderboard b
            GROUP BY id
            order by score desc) tab
) spec
where spec.rank=
(select rank+1
from (
   select tab.id, tab.score, @rank :=@rank+1 as rank
     from (select @rank := 0) r,
            (SELECT b.id, max(b.score) as score
            FROM leaderboard b
            GROUP BY id
            order by score desc) tab
) spec
where spec.id=2)
order by rank;

请注意，您将特定 ID 放在子句 spec.id=2 上(我放了 2 是因为我必须更改环境中的值才能对其进行测试)

这是我测试的 SQL Fiddle，两个查询都有效:http://sqlfiddle.com/#!2/75047/2