主页 / dba / 问题 / 127271
Accepted
Josh Foskett
Asked: 2016-01-26 13:52:33 +0800 CST

如何根据数据获得总体排名而不考虑范围（所有项目、用户特定等）？

  • 772

表模式：

servers
- id
- user_id
- game_id
- slug
  ...

server_votes
- id
- server_id
- ip_address
- created_at
  ...

所以，我有两个表：servers和server_votes，我想根据每个服务器在一定时间内收到的票数来获得每个服务器的排名。我创建了以下查询来做到这一点（除了排名部分）：

SELECT 
    s.id,
    s.user_id,
    s.slug,
    (
        SELECT 
            COUNT(sv.id)
        FROM
            server_votes AS sv
        WHERE
            DATE(sv.created_at) > '2016-01-24 19:11:15'
        AND 
            sv.server_id = s.id
    ) AS votes
FROM
    servers AS s
LEFT JOIN
    server_votes AS sv 
ON 
    sv.server_id = s.id
WHERE 
    s.game_id = 1
GROUP BY
    s.id
ORDER BY 
    votes DESC,
    s.id ASC

上面的查询将返回以下内容：

*----*---------*---------*-------*
| id | user_id | slug    | votes |
*----*---------*---------*-------*
| 4  | 1       | Server4 | 3     |
| 2  | 1       | Server2 | 2     |
| 1  | 1       | Server1 | 0     |
| 3  | 2       | Server3 | 0     |
| 5  | 2       | Server5 | 0     |
*----*---------*---------*-------*

我想以以下内容结束：

*----*---------*---------*-------*------*
| id | user_id | slug    | votes | rank |
*----*---------*---------*-------*------*
| 4  | 1       | Server4 | 3     | 1    |
| 2  | 1       | Server2 | 2     | 2    |
| 1  | 1       | Server1 | 0     | 3    |
| 3  | 2       | Server3 | 0     | 4    |
| 5  | 2       | Server5 | 0     | 5    |
*----*---------*---------*-------*------*

或者，如果我正在查询特定用户的服务器，我会在所有服务器的上下文中获得排名：

*----*---------*---------*-------*------*
| id | user_id | slug    | votes | rank |
*----*---------*---------*-------*------*
| 3  | 2       | Server3 | 0     | 4    |
| 5  | 2       | Server5 | 0     | 5    |
*----*---------*---------*-------*------*

任何见解将不胜感激。

此外，这是对您有任何帮助的SQL Fiddle incase。

mysql rank

1 个回答

  1. Best Answer

    此查询使用变量进行排名：

    SELECT t.id,
    t.user_id,
    t.slug,
    t.votes,
    @rank := @rank + 1 AS rank
    FROM (
    SELECT 
        s.id,
        s.user_id,
        s.slug,
        (
            SELECT 
                COUNT(sv.id)
            FROM
                server_votes AS sv
            WHERE
                DATE(sv.created_at) > '2016-01-24 19:11:15'
            AND 
                sv.server_id = s.id
        ) AS votes
    FROM
        servers AS s
    LEFT JOIN
        server_votes AS sv 
    ON 
        sv.server_id = s.id
    WHERE 
        s.game_id = 1
    GROUP BY s.id
) t, (SELECT @rank := 0) r
ORDER BY 
    t.votes DESC,
    t.id ASC

    请参阅SQL 小提琴。

    输出：

    id  user_id slug    votes   rank
4   1       Server4 3       1
2   1       Server2 2       2
1   1       Server1 0       3
3   2       Server3 0       4
5   2       Server5 0       5

    一旦将上述查询放入带有 WHERE 子句的子查询中，它就会为特定用户输出数据：

    SELECT 
    r.id,
    r.user_id,
    r.slug,
    r.votes,
    r.rank
FROM (
   ...
) r
WHERE r.user_id = 2;

    它将输出 id 3 和 5。

    请参阅SQL 小提琴

    • 1

相关问题