在mysql表中对条目进行排名 [英] ranking entries in mysql table

问题描述

我有一个包含很多行的MySQL表.该表有一个受欢迎度列.如果按受欢迎程度排序，我可以获得每个项目的排名.是否可以在不对整个表格进行排序的情况下检索特定项目的等级?我不这么认为.正确吗?

I have a MySQL table with many rows. The table has a popularity column. If I sort by popularity, I can get the rank of each item. Is it possible to retrieve the rank of a particular item without sorting the entire table? I don't think so. Is that correct?

另一种方法是创建一个新列来存储等级，对整个表进行排序，然后遍历所有行并更新等级.那是非常低效的.也许有一种方法可以在单个查询中做到这一点?

An alternative would be to create a new column for storing rank, sort the entire table, and then loop through all the rows and update the rank. That is extremely inefficient. Is there perhaps a way to do this in a single query?

推荐答案

如果不先对表格进行排序或存储等级，就无法计算事物的顺序(称为等级).

There is no way to calculate the order (what you call rank) of something without first sorting the table or storing the rank.

但是，如果您的表已正确索引(受欢迎程度索引)，则数据库对其进行排序很简单，因此您可以获得排名.我建议类似以下内容:

If your table is properly indexed however (index on popularity) it is trivial for the database to sort this so you can get your rank. I'd suggest something like the following:

SET @rank := 0;
SELECT t.*, @rank := @rank + 1
FROM table t
ORDER BY t.popularity;

要获取具有特定"id"的项目，则只需使用子查询，如下所示:

To fetch an item with a specific "id" then you can simply use a subquery as follows:

SET @rank := 0;
SELECT * FROM (
  SELECT t.*, @rank := @rank + 1
  FROM table t
  ORDER BY t.popularity
) t2
WHERE t2.id = 1;

这篇关于在mysql表中对条目进行排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！