mysql - 如何在 codeigniter 中执行自定义查询 - 分数表中用户的排名

Question

我有一个这样的表:

id     user_id     score

1        16433       20
2        16433       10
3        14621       12
4        47899       10
5        13220       30
6        14621       15   

我的表名称是game_scores。现在，我想在分数表中获取用户的排名(或限制为 8 个用户):

$query = $this->db->query("

                        SET @rnk=0; SET @rank=0; SET @curscore=0;
                        SELECT score,id,rank FROM
                        (
                            SELECT AA.*,BB.ID,
                            (@rnk:=@rnk+1) rnk,
                            (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
                            (@curscore:=score) newscore
                            FROM
                            (
                                SELECT * FROM
                                (SELECT COUNT(1) scorecount,score
                                FROM game_scores GROUP BY score
                            ) AAA
                            ORDER BY score DESC
                        ) AA LEFT JOIN game_scores BB USING (score)) A;

                        ");

return $query;

但它返回 false 。

我从此链接获取了此代码:

https://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table

Answer 1

您可以使用如下查询:

SELECT 
@rank := (@rank+1) AS rank,
sc.user_id , sc.score
FROM
(
  SELECT user_id , max(score) AS score
  FROM myscore
  GROUP BY user_id
  ORDER BY score DESC
  LIMIT 8
) AS sc
CROSS JOIN ( SELECT @rank := 0) AS param;

示例

MariaDB [yourschema]> select * from myscore;
+----+---------+-------+
| id | user_id | score |
+----+---------+-------+
|  1 |   16433 |    20 |
|  2 |   16433 |    10 |
|  3 |   14621 |    12 |
|  4 |   47899 |    10 |
|  5 |   13220 |    30 |
|  6 |   14621 |    15 |
|  7 |   47891 |    10 |
|  8 |   13222 |    30 |
|  9 |   14623 |    15 |
+----+---------+-------+
9 rows in set (0.00 sec)

MariaDB [yourschema]> SELECT
    -> @rank := (@rank+1) AS rank,
    -> sc.user_id , sc.score
    -> FROM
    -> (
    ->   SELECT user_id , max(score) AS score
    ->   FROM myscore
    ->   GROUP BY user_id
    ->   ORDER BY score DESC
    ->   LIMIT 8
    -> ) AS sc
    -> CROSS JOIN ( SELECT @rank := 0) AS param;
+------+---------+-------+
| rank | user_id | score |
+------+---------+-------+
|    1 |   13220 |    30 |
|    2 |   13222 |    30 |
|    3 |   16433 |    20 |
|    4 |   14621 |    15 |
|    5 |   14623 |    15 |
|    6 |   47899 |    10 |
|    7 |   47891 |    10 |
+------+---------+-------+
7 rows in set (0.00 sec)

MariaDB [yourschema]>