按select result group添加rank列

xmjla07d 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(342)

我有一个名为mytable的表，其中包含参与者列表和他们在每个测试中的分数。

-----------------------------------------
id_test     id_paticipant   score
-----------------------------------------
id_test1    Partcipant1      100
id_test1    Partcipant2      200
id_test1    Partcipant3      150
id_test1    Partcipant4      300
id_test2    Partcipant1      500
id_test2    Partcipant3      250
id_test3    Partcipant2       70
id_test3    Partcipant3      150
id_test3    Partcipant4      420
id_test4    Partcipant1      120
id_test4    Partcipant2      200

我想有一个请求，允许我有一个排名栏，在那里我将有在每个测试，如下表中的每个参与者的排名。

-----------------------------------------------
id_test     id_paticipant   score   rank
-------------------------------------------------------
id_test1    Partcipant1      100    4
id_test1    Partcipant2      200    2
id_test1    Partcipant3      150    3
id_test1    Partcipant4      300    1
id_test2    Partcipant1      500    1
id_test2    Partcipant3      250    2
id_test3    Partcipant2       70    3
id_test3    Partcipant3      150    2
id_test3    Partcipant4      420    1
id_test4    Partcipant1      120    2
id_test4    Partcipant2      200    1
---------------------------------------------

我试过这个：

SET @prev_id_test := 0;
SET @curRow := 1;
select t2.id_test, t2.id_partcipant, t2.score
,if(t2.id_test=@prev_id_test, @curRow := @curRow + 1, @curRow := 1) AS rank, @prev_id_test := t2.id_test 
from (select myTable.* from myTable order by myTable.id_test, myTable.score desc) as t2 
order by t2.id_test, t2.score, t2.id_partcipant desc;

但它不起作用，我的排名是按降序排列的，我的意思是最低分的排名是1，等等。。。比如：

-----------------------------------------------
id_test     id_paticipant   score   rank
-----------------------------------------------
id_test1    Partcipant1      100    1
id_test1    Partcipant2      200    3
id_test1    Partcipant3      150    2
id_test1    Partcipant4      300    4
id_test2    Partcipant1      500    2
id_test2    Partcipant3      250    1
id_test3    Partcipant2       70    1
id_test3    Partcipant3      150    2
id_test3    Partcipant4      420    3
id_test4    Partcipant1      120    1
id_test4    Partcipant2      200    2
---------------------------------------------

有人能帮忙吗？
提前谢谢

sql mysql group-by rank sql-order-by

来源：https://stackoverflow.com/questions/50815497/add-rank-column-by-select-result-group

2条答案

按热度按时间

4xrmg8kj1#

我现在要添加3个其他列：
包含每组最大得分的max，
包含每组最小得分的最小值，
和平均值，包含每组的平均分
如下表所示：

------------------------------------------------------------------------
id_test     id_participant  score   rank    Max   Min   average
------------------------------------------------------------------------
id_test1    Partcipant1      100     4      300   100    187.5
id_test1    Partcipant2      200     2      300   100    187.5
id_test1    Partcipant3      150     3      300   100    187.5
id_test1    Partcipant4      300     1      300   100    187.5
id_test2    Partcipant1      500     1      500   250    375
id_test2    Partcipant3      250     2      500   250    375
id_test3    Partcipant2       70     3      420    70    213.33
id_test3    Partcipant3      150     2      420    70    213.33
id_test3    Partcipant4      420     1      420    70    213.33
id_test4    Partcipant1      120     2      200   120    160
id_test4    Partcipant2      200     1      200   120    160
------------------------------------------------------------------------

我试试这个：

select  
    t.*,
    (@rn := if(@it = t.id_test, @rn + 1,if(@it := t.id_test, 1, 1))) as rank,
    (select max(t1.score) from myTables t1 group by t1.id_test) as Max,
    (select min(t1.score) from myTables t1 group by t1.id_test) as Max,
    (select round((sum(t1.score)/count(t1.id_participant)), 2) from myTables t1 group by t1.id_test) as average
from
    (select t.* 
     from myTables 
     order by t.id_test, t.score desc) as t 
cross join 
    (select @it := -1, @rn := 0) params;

但我有个错误：
无法重新打开表
我认为这是由于表mytable是临时表这一事实造成的。
你有什么想法吗？
谢谢

赞(0）回复(0）举报 2021-06-21

c9x0cxw02#

尝试此版本：

select t.*,
       (@rn := if(@it = t.id_test, @rn + 1,
                  if(@it := t.id_test, 1, 1)
                 )
       ) as rank
from (select t.*
      from myTables t
      order by id_test, score desc
     ) t cross join
     (select @it := -1, @rn := 0) params;

有什么区别？您的版本是分配变量并在不同的表达式中引用它们。mysql（或任何其他数据库）保证表达式在 SELECT . 所以，你不知道哪个先发生。
而且，mysql v8+最终使变量的使用变得不必要。它与sql社区的其他成员一起支持窗口函数。这将逻辑简化为：

row_number() over (partition by test_id order by score_desc) as ranking

赞(0）回复(0）举报 2021-06-21

我来回答

按select result group添加rank列

2条答案

相关问题

热门标签

最新问答