<bdo id='YMCDI'></bdo><ul id='YMCDI'></ul>
<i id='YMCDI'><tr id='YMCDI'><dt id='YMCDI'><q id='YMCDI'><span id='YMCDI'><b id='YMCDI'><form id='YMCDI'><ins id='YMCDI'></ins><ul id='YMCDI'></ul><sub id='YMCDI'></sub></form><legend id='YMCDI'></legend><bdo id='YMCDI'><pre id='YMCDI'><center id='YMCDI'></center></pre></bdo></b><th id='YMCDI'></th></span></q></dt></tr></i><div id='YMCDI'><tfoot id='YMCDI'></tfoot><dl id='YMCDI'><fieldset id='YMCDI'></fieldset></dl></div>

    1. <tfoot id='YMCDI'></tfoot>
    2. <legend id='YMCDI'><style id='YMCDI'><dir id='YMCDI'><q id='YMCDI'></q></dir></style></legend>

      <small id='YMCDI'></small><noframes id='YMCDI'>

        根据 MySQL 中的多列确定排名

        时间:2023-05-24
          <tbody id='UvcNP'></tbody>

          <small id='UvcNP'></small><noframes id='UvcNP'>

          • <tfoot id='UvcNP'></tfoot>
            <i id='UvcNP'><tr id='UvcNP'><dt id='UvcNP'><q id='UvcNP'><span id='UvcNP'><b id='UvcNP'><form id='UvcNP'><ins id='UvcNP'></ins><ul id='UvcNP'></ul><sub id='UvcNP'></sub></form><legend id='UvcNP'></legend><bdo id='UvcNP'><pre id='UvcNP'><center id='UvcNP'></center></pre></bdo></b><th id='UvcNP'></th></span></q></dt></tr></i><div id='UvcNP'><tfoot id='UvcNP'></tfoot><dl id='UvcNP'><fieldset id='UvcNP'></fieldset></dl></div>

            1. <legend id='UvcNP'><style id='UvcNP'><dir id='UvcNP'><q id='UvcNP'></q></dir></style></legend>

                • <bdo id='UvcNP'></bdo><ul id='UvcNP'></ul>
                • 本文介绍了根据 MySQL 中的多列确定排名的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  限时送ChatGPT账号..

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

                  I have a table which has 3 fields, I want to rank column based on user_id and game_id.

                  这是 SQL 小提琴:http://sqlfiddle.com/#!9/883e9d/1

                  Here is 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 的顺序

                  order of game_detail

                  推荐答案

                  在 派生表(FROM 子句中的子查询),我们对数据进行排序,使得具有相同user_id 值的所有行聚集在一起,并进一步根据 game_detail 按降序在它们之间排序.

                  In a Derived Table (subquery inside the FROM clause), we order our data such that all the rows having same user_id values come together, with further sorting between them based on game_detail in Descending order.

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

                  Now, we use this result-set and use conditional CASE..WHEN expressions to evaluate the row numbering. It will be like a Looping technique (which we use in application code, eg: PHP). We would store the previous row values in the User-defined variables, and then check the current row's value(s) against the previous row. Eventually, we will assign row number accordingly.

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

                  Based on MySQL docs and @Gordon Linoff's observation:

                  涉及用户变量的表达式的求值顺序是不明确的.例如,不能保证 SELECT @a, @a:=@a+1先计算@a,然后执行赋值.

                  The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

                  我们需要计算行号并将 user_id 值分配给同一表达式中的 @u 变量.

                  We will need to evaluate row number and assign the user_id value to @u variable within the same expression.

                  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      |
                  

                  查看 DB Fiddle

                  来自 MySQL Docs 的有趣说明,其中我最近发现:

                  An interesting note from MySQL Docs, which I discovered recently:

                  以前的 MySQL 版本可以将值分配给SET 以外的语句中的用户变量.这个功能是MySQL 8.0 支持向后兼容,但受制于在未来的 MySQL 版本中删除.

                  Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.

                  另外,感谢一位 SO 成员,看到了 MySQL 团队的这篇博客:https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/

                  Also, thanks to a fellow SO member, came across this blog by MySQL Team: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/

                  一般观察是,使用 ORDER BY 并评估同一查询块中的用户变量,并不能确保值始终正确.因为,MySQL 优化器可能到位并改变我们假定的评估顺序.

                  General observation is that using ORDER BY with evaluation of the user variables in the same query block, does not ensure that the values will be correct always. As, MySQL optimizer may come into place and change our presumed order of evaluation.

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

                  Best approach to this problem would be to upgrade to MySQL 8+ and utilize the Row_Number() functionality:

                  架构(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              |
                  

                  查看 DB Fiddle

                  这篇关于根据 MySQL 中的多列确定排名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:MySQL 工作台:查询错误 (1064):第 1 行“可见"附近的语法错误 下一篇:MySql 单表,选择过去 7 天并包含空行

                  相关文章

                    <bdo id='9dmkt'></bdo><ul id='9dmkt'></ul>

                  <i id='9dmkt'><tr id='9dmkt'><dt id='9dmkt'><q id='9dmkt'><span id='9dmkt'><b id='9dmkt'><form id='9dmkt'><ins id='9dmkt'></ins><ul id='9dmkt'></ul><sub id='9dmkt'></sub></form><legend id='9dmkt'></legend><bdo id='9dmkt'><pre id='9dmkt'><center id='9dmkt'></center></pre></bdo></b><th id='9dmkt'></th></span></q></dt></tr></i><div id='9dmkt'><tfoot id='9dmkt'></tfoot><dl id='9dmkt'><fieldset id='9dmkt'></fieldset></dl></div>
                • <tfoot id='9dmkt'></tfoot>

                  <small id='9dmkt'></small><noframes id='9dmkt'>

                    1. <legend id='9dmkt'><style id='9dmkt'><dir id='9dmkt'><q id='9dmkt'></q></dir></style></legend>