使用组内的记录排名更新 MySQL 表 [英] Update a MySQL table with record rankings within groups
问题描述
我有一个名为winners"的表格，其中包含id"、category"、score"、rank"列.
I have a table called 'winners' with columns 'id', 'category', 'score', 'rank'.
我需要更新我的表格并在子类别 (category_id) 中指定一个排名，根据样本，该排名是 2，但将来可能会更多.
I need to update my table and asign a rank within the subcategories (category_id) which as per the sample is 2 but could be more than that in the future.
我发现的大多数答案都是基于 select 语句，它们只是倾向于只输出表格视图，但我确实找到了一个非常好的更新"答案(https://stackoverflow.com/a/2727239/4560380) 特别是答案更新，其中需要平局才能共享相同的排名.
Most anwers I've found are based around select statements which simply tends to just output the table view but I did find a very good 'Update' answer (https://stackoverflow.com/a/2727239/4560380) specifically the answer update where ties are required to share the same rank.
样本
CREATE TABLE winners (
id int,
category_id int,
score double,
rank int
);
INSERT INTO winners VALUES
(1, 1, 4.36, NULL),
(2, 1, 2.35, NULL),
(3, 1, 1.25, NULL),
(4, 2, 4.21, NULL),
(5, 2, 3.33, NULL),
(6, 1, 4.24, NULL),
(7, 1, 1.22, NULL),
(8, 1, 1.25, NULL),
(9, 2, 4.21, NULL),
(10, 2, 3.63, NULL);
+----------+-------------+-------+------+
| id | category_id | score | rank |
+----------+-------------+-------+------+
| 1 | 1 | 4.36 | |
| 2 | 1 | 2.35 | |
| 3 | 1 | 1.25 | |
| 4 | 2 | 4.21 | |
| 5 | 2 | 3.33 | |
| 6 | 1 | 4.24 | |
| 7 | 1 | 1.22 | |
| 8 | 1 | 1.25 | |
| 9 | 2 | 4.21 | |
| 10 | 2 | 3.63 | |
+----------+-------------+-------+------+
当只有一个类别需要担心时，上面的链接答案非常适用于数据，但当有多个类别或子组需要在其中进行排名时则不行.
The linked answer above works perfectly for the data when there is only one category to worry about but not when there are multiple categories or subgroups to rank within.
我曾尝试在代码中添加 where 子句(第 8 行)
I had attempted to add in a where clause to the code (line 8)
1. UPDATE winners
2. JOIN (SELECT w.score,
3. IF(@lastPoint <> w.score,
4. @curRank := @curRank + 1,
5. @curRank) AS rank,
6. @lastPoint := w.rating
7. FROM winners w
8. WHERE category_id = 1
9. JOIN (SELECT @curRank := 0, @lastPoint := 0) r
10. ORDER BY w.score DESC
11. ) ranks ON (ranks.score = winners.score)
12. SET winners.rank = ranks.rank;
打算为每个 category_id 运行代码两次，但脚本失败.
with the intention of attempting to run the code twice for each category_id but the script fails.
为多个类别修改上述答案的任何选项都很棒.
Any options on modifying the answer above for multiple categories would be fantastic.
需要澄清的结果(按类别排名).
Needed result just to clarify (ranked within categories).
+----------+-------------+-------+------+
| id | category_id | score | rank |
+----------+-------------+-------+------+
| 1 | 1 | 4.36 | 1 |
| 6 | 1 | 4.24 | 2 |
| 2 | 1 | 2.35 | 3 |
| 8 | 1 | 1.25 | 4 |
| 3 | 1 | 1.25 | 4 |
| 7 | 1 | 1.22 | 5 |
| 4 | 2 | 4.21 | 1 |
| 9 | 2 | 4.21 | 1 |
| 10 | 2 | 3.63 | 2 |
| 5 | 2 | 3.33 | 3 |
+----------+-------------+-------+------+
谢谢各位！
更新
设法找到我拥有的另一段代码 https://stackoverflow.com/a/13270603/4560380不知何故最初错过了，并且能够成功地使用每个 category_id 的 where 子句对其进行修改.这不是一种理想的方式 - 为多个类别运行多次，但目前还可以.
Managed to find another bit of code https://stackoverflow.com/a/13270603/4560380 that I had somehow originally missed and was able to modifiy it with the where clause for each category_id succesfully. Not an ideal way of doing it - running multiple times for multiple categories but at this point in time it is fine.
set @currentRank = 0,
@lastRating = null,
@rowNumber = 1;
update winners r
inner join (
select
score,
@currentRank := if(@lastRating = score, @currentRank, @rowNumber) rank,
@rowNumber := @rowNumber + if(@lastRating = score, 0, 1) rowNumber,
@lastRating := score
from winners
where category_id = 1
order by score desc
) var on var.score = r.score
set r.rank = var.rank
对于在 1 次脚本运行中更自动"处理排名中的多个类别的进一步答案仍然非常欢迎和赞赏.
further answers for a more 'automatic' handling of multiple categories within the ranking in 1 script run are still very welcome and appreciated.
谢谢
更新
刚刚注意到我找到的答案不能很好地处理零分 (0.00)，并将它们排在其他分数的中间.
Just noticed that the answer that I found doesn't deal with zero scores (0.00) very well and places them ranked in the middle of other scores.
下面的 shawnt00 答案有效并且正确评估零分.https://stackoverflow.com/a/34667112/4560380
shawnt00 answer below is working and evaluates zero scores correctly. https://stackoverflow.com/a/34667112/4560380
推荐答案
update winners
set rank = (
select count(score) + 1
from winners w2
where w2.category_id = winners.category_id and w2.score > winners.score
)
count(*) 将评估为零，即使没有符合条件的行，即最高排名.如果你想要一个密集的等级，你可以这样做:
count(*) will evaluate to zero even when no rows match the condition, i.e., the top ranking. If you wanted a dense rank you could do this:
update winners
set rank = (
select count(distinct score) + 1
from winners w2
where w2.category_id = winners.category_id and w2.score > winners.score
)
根据您的评论，我发现了一个有效的查询.(它适用于 SQL Server，我不熟悉 MySQL 的怪癖.)http://sqlfiddle.com/#!9/1159f/1
Per your comment I've found a query that does work. (It works on SQL Server and I'm not familiar with MySQL's quirks.) http://sqlfiddle.com/#!9/1159f/1
update winners
inner join (
select w.id, w.category_id, count(w2.score) + 1 rank
from winners w left outer join winners w2
on w2.category_id = w.category_id and w2.score > w.score
group by w.id
) r
on r.id = winners.id
set winners.rank = r.rank
这篇关于使用组内的记录排名更新 MySQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
