MySQL 在 ORDER BY 中获取行位置

MySQL get row position in ORDER BY

提问by leepowers

With the following MySQL table:

使用以下 MySQL 表：

+-----------------------------+
+ id INT UNSIGNED             +
+ name VARCHAR(100)           +
+-----------------------------+

How can I select a singlerow AND its position amongst the other rows in the table, when sorted by name ASC. So if the table data looks like this, when sorted by name:

如何选择一个单一的行和其在表中的其他行之间的位置，当排序name ASC。因此，如果表数据如下所示，按名称排序时：

+-----------------------------+
+ id | name                   +
+-----------------------------+
+  5 | Alpha                  +
+  7 | Beta                   +
+  3 | Delta                  +
+ .....                       +
+  1 | Zed                    +
+-----------------------------+

How could I select the Betarow getting the current position of that row? The result set I'm looking for would be something like this:

如何选择Beta获取该行当前位置的行？我正在寻找的结果集将是这样的：

+-----------------------------+
+ id | position | name        +
+-----------------------------+
+  7 |        2 | Beta        +
+-----------------------------+

I can do a simple SELECT * FROM tbl ORDER BY name ASCthen enumerate the rows in PHP, but it seems wasteful to load a potentially large resultset just for a single row.

我可以做一个简单的SELECT * FROM tbl ORDER BY name ASC然后枚举 PHP 中的行，但是只为单行加载一个可能很大的结果集似乎很浪费。

回答by OMG Ponies

Use this:

用这个：

SELECT x.id, 
       x.position,
       x.name
  FROM (SELECT t.id,
               t.name,
               @rownum := @rownum + 1 AS position
          FROM TABLE t
          JOIN (SELECT @rownum := 0) r
      ORDER BY t.name) x
 WHERE x.name = 'Beta'

...to get a unique position value. This:

...获得唯一的位置值。这个：

SELECT t.id,
       (SELECT COUNT(*)
          FROM TABLE x
         WHERE x.name <= t.name) AS position,
       t.name    
  FROM TABLE t      
 WHERE t.name = 'Beta'

...will give ties the same value. IE: If there are two values at second place, they'll both have a position of 2 when the first query will give a position of 2 to one of them, and 3 to the other...

...会给领带相同的价值。IE：如果有两个值排在第二位，那么当第一个查询将位置 2 给其中一个时，它们的位置都为 2，另一个位置为 3...

回答by zerkms

This is the only way that I can think of:

这是我能想到的唯一方法：

SELECT `id`,
       (SELECT COUNT(*) FROM `table` WHERE `name` <= 'Beta') AS `position`,
       `name`
FROM `table`
WHERE `name` = 'Beta'

回答by Max

If the query is simple and the size of returned result set is potentially large, then you may try to split it into two queries.

如果查询比较简单，返回结果集的大小可能很大，那么你可以尝试将其拆分为两个查询。

The first query with a narrow-down filtering criteria just to retrieve data of that row, and the second query uses COUNTwith WHEREclause to calculate the position.

第一个带有缩小过滤条件的查询只是为了检索该行的数据，第二个查询使用COUNTwithWHERE子句来计算位置。

For example in your case

例如在你的情况

Query 1:

查询 1：

SELECT * FROM tbl WHERE name = 'Beta'

SELECT * FROM tbl WHERE name = 'Beta'

Query 2:

查询 2：

SELECT COUNT(1) FROM tbl WHERE name >= 'Beta'

SELECT COUNT(1) FROM tbl WHERE name >= 'Beta'

We use this approach in a table with 2M record and this is way more scalable than OMG Ponies's approach.

我们在具有 2M 记录的表中使用这种方法，这比 OMG Ponies 的方法更具可扩展性。

回答by Kai Noack

The other answers seem too complicated for me.

其他答案对我来说似乎太复杂了。

Here comes an easy example, let's say you have a table with columns:

这是一个简单的示例，假设您有一个包含列的表：

userid | points

and you want to sort the userids by points and get the row position (the "ranking" of the user), then you use:

并且您想按点对用户 ID 进行排序并获取行位置（用户的“排名”），然后使用：

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) AS num, userid, points
FROM
    ourtable
ORDER BY points DESC

numgives you the row postion (ranking).

num给你行位置（排名）。

If you have MySQL 8.0+ then you might want to use ROW_NUMBER()

如果你有 MySQL 8.0+，那么你可能想使用ROW_NUMBER()

回答by NVG

The position of a row in the table represents how many rows are "better" than the targeted row.

表中一行的位置表示有多少行比目标行“更好”。

So, you must count those rows.

因此，您必须计算这些行。

SELECT COUNT(*)+1 FROM tableWHERE name<'Beta'

SELECT COUNT(*)+1 FROM tableWHERE name<'Beta'

In case of a tie, the highest position is returned.

如果出现平局，则返回最高位置。

If you add another row with same name of "Beta" after the existing "Beta" row, then the position returned would be still 2, as they would share same place in the classification.

如果您在现有的“Beta”行之后添加另一行“Beta”同名，则返回的位置仍为 2，因为它们将在分类中共享相同的位置。

Hope this helps people that will search for something similar in the future, as I believe that the question owner already solved his issue.

希望这可以帮助将来搜索类似内容的人，因为我相信问题所有者已经解决了他的问题。

回答by Damián Rafael Lattenero

I've got a very very similar issue, that's why I won't ask the same question, but I will share here what did I do, I had to use also a group by, and order by AVG. There are students, with signatures and socore, and I had to rank them (in other words, I first calc the AVG, then order them in DESC, and then finally I needed to add the position (rank for me), So I did something Very similaras the best answerhere, with a little changes that adjust to my problem):

我有一个非常相似的问题，这就是为什么我不会问同样的问题，但我会在这里分享我做了什么，我还必须使用 group by 和 order by AVG。有学生，有签名和socore，我必须对他们进行排名（换句话说，我首先计算AVG，然后在DESC中对他们进行排序，最后我需要添加位置（对我来说排名），所以我做了一些非常相似的最佳答案在这里，与调整我的问题），一个小的变化：

I put finally the position(rank for me) column in the external SELECT

我最终将position（对我而言的排名）列放在外部 SELECT 中

SET @rank=0;
SELECT @rank := @rank + 1 AS ranking, t.avg, t.name
  FROM(SELECT avg(students_signatures.score) as avg, students.name as name
FROM alumnos_materia
JOIN (SELECT @rownum := 0) r
left JOIN students ON students.id=students_signatures.id_student
GROUP BY students.name order by avg DESC) t 

回答by Davinder Singh

I was going through the accepted answer and it seemed bit complicated so here is the simplified version of it.

我正在浏览已接受的答案，看起来有点复杂，所以这里是它的简化版本。

SELECT t,COUNT(*) AS position FROM t      
 WHERE name <= 'search string' ORDER BY name

回答by Bedram Tamang

I have similar types of problem where I require rank(Index) of table order by votes desc. The following works fine with for me.

我有类似类型的问题，我需要table 的rank( Index) order by votes desc。以下对我来说很好用。

Select *, ROW_NUMBER() OVER(ORDER BY votes DESC) as "rank"
From "category_model"
where ("model_type" = ? and "category_id" = ?)

回答by Eka Rudito

may be what you need is with add syntax

可能是你需要的是添加语法

LIMIT

so use

所以用

SELECT * FROM tbl ORDER BY name ASC LIMIT 1

if you just need one row..

如果你只需要一排..