logo标签列表

MySQL获取按特定字段排序后的行位置

sqlmysqlanalytic-functions
98

以下是MySQL表：

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

如何选择一行数据并获取该行在表格中按 name ASC 排序后的位置？假设按名称排序后，表格如下：

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

如何选择“Beta”行并获取该行的当前位置？我要查找的结果集应该类似于这样：

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

我可以使用简单的SELECT * FROM tbl ORDER BY name ASC语句查询数据，然后在PHP中枚举行，但仅为获取一行而加载可能很大的结果集似乎有些浪费。

https://dev59.com/7HE85IYBdhLWcg3w9orw - Ciro Santilli OurBigBook.com
1
可能是MySQL-在选择时获取行号的重复问题。 - jberryman
9个回答
134
请使用以下内容：
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'

...获取唯一的位置值。这样做：

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

...会给予相同的值。例如：如果第二个位置有两个值，当第一个查询给其中一个位置2时，它们两个都将具有2的位置，而另一个则具有3的位置...

@actual: 没有什么可说的 - 除了转移到支持分析函数的竞争对手（PostgreSQL、Oracle、SQL Server、DB2...）之外，没有其他选择。 - OMG Ponies
2
@OMGPonies 只是忘记在 position 后面加逗号，但完美无缺。 - pierallard
我知道这篇文章已经很老了，但我需要类似的解决方案。然而，当使用内连接、分组和排序时，"position"字段会忽略这些条件，导致值混乱。有什么解决方法吗？ - Daniel
@Daniel 你应该提出一个新的问题，可能会涉及到这个问题。 - PeerBr
@actual，由于这是针对单行的查询，因此在性能方面不应该有显着的问题。如果您正在尝试获取完整列表的排名，则情况不同，但是您可以通过按点数排序并使用隐式排名来“作弊”。 - AgmLauncher
显示剩余3条评论
20

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

SELECT `id`,
       (SELECT COUNT(*) FROM `table` WHERE `name` <= 'Beta') AS `position`,
       `name`
FROM `table`
WHERE `name` = 'Beta'
2
+1 不错的技巧... 但是你可能想使用 name <= 'Beta'。 - Daniel Vassallo
这种方法将为并列的情况提供相同的“position”值。 - OMG Ponies
如果在其中添加 LIMIT 1，会怎么样呢？如果出现平局，您将只获取最后一个位置的一行。 - Daniel Vassallo
如果 OP 可以保证 name 字段是唯一的 - 那么没有理由使查询更加复杂。如果他不能保证 - 那么让我们等待他对于重名结果的期望。 - zerkms
8
如果查询简单且返回结果集的大小可能很大，则可以尝试将其拆分为两个查询。第一个查询使用缩小过滤条件仅检索该行的数据，第二个查询使用带有WHERE子句的COUNT计算位置。
例如，在您的情况下：
查询1：
SELECT * FROM tbl WHERE name = 'Beta'
查询2：
SELECT COUNT(1) FROM tbl WHERE name >= 'Beta'
我们在具有2M记录的表中使用此方法，这比OMG Ponies的方法更具可扩展性。
6
其他答案对我来说似乎太复杂了。
以下是一个简单的示例，假设您有一个带有列的表格：
userid | points

如果您想按积分对用户id进行排序并获取行位置（即用户的“排名”），则可以使用以下代码：

SET @row_number = 0;

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

num给出了行的位置(排名)。

如果您使用MySQL 8.0+，则可能希望使用ROW_NUMBER()

3
表中一行的位置表示有多少行“比”目标行更好。因此，您需要计算这些行。
SELECT COUNT(*)+1 FROM table WHERE name<'Beta'
如果存在平局，则返回最高位置。
如果您在现有的“Beta”行之后添加另一行具有相同名称的“Beta”，则返回的位置仍为2，因为它们将共享分类中的同一位置。
希望这可以帮助将来搜索类似内容的人，因为我相信问题所有者已经解决了他的问题。
3
我有一个非常相似的问题，所以我不会问同样的问题，但我会在这里分享我做了什么。我必须使用 group by 和 order by AVG。有学生，有签名和得分，我必须对他们进行排名（换句话说，我先计算平均值，然后按照降序排序，最后我需要添加位置（对我来说是排名），因此我做了一些非常相似的事情作为这里的最佳答案，稍微更改一下以适应我的问题）：
最后，我将“位置”（对我而言是排名）列放在外部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
这个答案比被采纳的那个更容易理解。+1 - Eric Seastrand
2

我正在查阅被接受的答案，但它似乎有些复杂，因此这里是简化版本。

最初的回答：

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

我有类似的问题，需要获取表格 order by votes desc 的排名（Index）。以下代码对我很有效。

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

也许你需要的是使用 add 语法

LIMIT

所以使用

SELECT * FROM tbl ORDER BY name ASC LIMIT 1

如果你只需要一行数据..

4
这个答案没有解决这里的问题。你可以考虑将其删除。 - developer_hatch
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接