当前位置: 首页 > 面试题库 >

根据MySQL中的多列确定排名

虞承泽
2023-03-14
问题内容

我有一个包含3个字段的表,我想根据user_id和game_id对列进行排名。

这是SQL Fiddle:http
://sqlfiddle.com/#!9/883e9d/1

我已经在桌子上了:

 user_id | game_id |   game_detial_sum  |
 --------|---------|--------------------|
 6       | 10      |  1000              |   
 6       | 11      |  260               |
 7       | 10      |  1200              |
 7       | 11      |  500               |
 7       | 12      |  360               |
 7       | 13      |  50                |

预期产量:

user_id  | game_id |   game_detial_sum  |  user_game_rank  |
 --------|---------|--------------------|------------------|
 6       | 10      |  1000              |   1              |
 6       | 11      |  260               |   2              |
 7       | 10      |  1200              |   1              |
 7       | 11      |  500               |   2              |
 7       | 12      |  360               |   3              |
 7       | 13      |  50                |   4              |

到目前为止,我的努力:

SET @s := 0; 
SELECT user_id,game_id,game_detail, 
       CASE WHEN user_id = user_id THEN (@s:=@s+1) 
            ELSE @s = 0 
       END As user_game_rank 
FROM game_logs

编辑:( 来自OP 注释):排序基于的降序game_detail

game_detail的顺序


问题答案:

在派生表(子句内的子查询FROM)中,我们对数据user_id进行排序,以使所有具有相同值的行放在一起,并根据game_detail降序对它们进行进一步排序。

现在,我们使用此结果集并使用条件CASE..WHEN表达式来评估行编号。就像循环技术(我们在应用程序代码中使用的,例如:PHP)一样。我们将前一行的值存储在用户定义的变量中,然后对照前一行检查当前行的值。最终,我们将相应地分配行号。

编辑: 基于MySQL 文档和@Gordon Linoff的观察:

涉及用户变量的表达式的求值顺序不确定。例如,不能保证SELECT @a,@a:= @ a + 1首先评估@a,然后执行分配。

我们将需要评估行号,并将user_id值分配给@u同一表达式中的变量。

SET @r := 0, @u := 0; 
SELECT
  @r := CASE WHEN @u = dt.user_id 
                  THEN @r + 1
             WHEN @u := dt.user_id /* Notice := instead of = */
                  THEN 1 
        END AS user_game_rank, 
  dt.user_id, 
  dt.game_detail, 
  dt.game_id

FROM 
( SELECT user_id, game_id, game_detail
  FROM game_logs 
  ORDER BY user_id, game_detail DESC 
) AS dt

结果

| user_game_rank | user_id | game_detail | game_id |
| -------------- | ------- | ----------- | ------- |
| 1              | 6       | 260         | 11      |
| 2              | 6       | 100         | 10      |
| 1              | 7       | 1200        | 10      |
| 2              | 7       | 500         | 11      |
| 3              | 7       | 260         | 12      |
| 4              | 7       | 50          | 13      |

在数据库小提琴上查看

我最近发现了MySQL Docs的一个有趣的注释:

MySQL的早期版本使得可以在SET以外的语句中为用户变量分配值。MySQL
8.0支持此功能以实现向后兼容,但是在将来的MySQL版本中可能会删除该功能。

另外,由于有一个SO成员,MySQL团队访问了此博客:https :
//mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-
in-mysql-queries/

通常的观察是,ORDER BY在同一查询块中与用户变量的评估一起使用时,并不能确保值始终正确。至于,MySQL优化 可以 接触到的地方,并改变我们的
假设 计算顺序。

解决此问题的最佳方法是升级到MySQL
8+并使用以下Row_Number()功能:

模式(MySQL v8.0)

SELECT user_id, 
       game_id, 
       game_detail, 
       ROW_NUMBER() OVER (PARTITION BY user_id 
                          ORDER BY game_detail DESC) AS user_game_rank 
FROM game_logs 
ORDER BY user_id, user_game_rank;

结果

| user_id | game_id | game_detail | user_game_rank |
| ------- | ------- | ----------- | -------------- |
| 6       | 11      | 260         | 1              |
| 6       | 10      | 100         | 2              |
| 7       | 10      | 1200        | 1              |
| 7       | 11      | 500         | 2              |
| 7       | 12      | 260         | 3              |
| 7       | 13      | 50          | 4              |

在数据库小提琴上查看



 类似资料:
  • 我有一个,有200多列。问题在于订单生成时 我需要对这些列进行如下排序: 我有办法在Python中做到这一点吗?

  • 问题内容: 我有一些数据,导入时会得到以下不需要的列,我正在寻找一种删除所有这些数据的简便方法 它们被0索引索引,所以我尝试了类似 但这不是很有效。我尝试编写一些for循环,但这使我感到震惊,因为熊猫的行为不佳。因此,我在这里问这个问题。 我已经看到了一些类似的示例(投递多列pandas),但这无法回答我的问题。 问题答案: 我不知道您所说的低效率是什么意思,但是如果您指的是打字,那么选择感兴趣的

  • 创建一个Java方法,它将列表作为参数(MasterList)并返回另一个列表(ExpectedList)。 列表中每个对象都有两个变量: null 我试图实现的逻辑是:当有多个ID相同的对象时,只考虑计数较大的特定对象。表示ID:有3个对象,所以我只考虑(ID:abc122,Count:20),因为在ID:abc122的对象中Count更高。在结束时,方法应返回

  • 问题内容: 这是表结构 我如何将表格显示为 上表显示升序I​​D,后跟与该ID相关联的parent_id,然后转到第二个ID,依此类推。我需要在单个查询中,是否可以这样做? 问题答案: 试试这个: http://sqlfiddle.com/#!2/9b05f/3 编辑: 鉴于这个答案一直在不断增加,我重新考虑了这个问题并发现了一个缺陷。如果由于某种原因,父级的ID高于子级的ID,则排序会混乱。仅当

  • 本文向大家介绍MySQL查询显示多列的排名?,包括了MySQL查询显示多列的排名?的使用技巧和注意事项,需要的朋友参考一下 为此,请使用FIND_IN_SET()方法。让我们首先创建一个表- 使用插入命令在表中插入一些记录- 使用select语句显示表中的所有记录- 这将产生以下输出- 以下是显示多列等级的查询- 这将产生以下输出,显示基于标记和年龄的排名-