MySQL中的ROW_NUMBER()函数

Question

MySQL中的ROW_NUMBER()函数

mysqlsqlgreatest-n-per-grouprankrow-number

331

在MySQL中有没有类似于SQL Server函数 ROW_NUMBER() 的好用的方法可以实现相似功能？

比如：

SELECT 
    col1, col2, 
    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1

我可以举个例子，比如添加一个条件限制intRow为1，以获取每个(col1, col2)对中最高col3的单行。

- Paul

对于一个简单的MySQL行号函数，请查看http://datamakessense.com/mysql-rownum-row-number-function/。 - AdrianBR

6

MySql 8现在已经有ROW_NUMBER()和RANK()函数了，请查看下面的答案。 - Jim Davis

1

@JimDavis 是的，这是 https://dev59.com/enI-5IYBdhLWcg3wW3Cp#46753800。文档：https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number。 - flow2k

27个回答

121

我想要每个(col1, col2) 对中col3最高的一行。

这是一个组内最大值问题，这是最常见的SQL问题之一（因为似乎很简单，但实际上不太容易）。

我经常选择使用空自连接：

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

获取表中那些没有其他行 col1、col2 匹配并且 col3 更高的行。（你会注意到这个问题以及大多数其他分组最大值解决方案，如果有多行具有相同的 col1、col2 和 col3，则会返回多个行。如果这是个问题，你可能需要一些后处理。）

- bobince

2

但是如果(col1,col2)对有两个col3的最大值呢？你将会得到两行。 - Paul

2

Bobince，这个解决方案在SO上相当受欢迎，但我有一个问题。该解决方案基本上与试图使用以下查询查找最大id的方式相同：SELECT t1.id FROM test t1 LEFT JOIN test t2 ON t1.id>t2.id WHERE t2.id IS NULL;是否需要n*n / 2 + n/2个IS NULL比较才能找到单行？是否发生了我没有看到的任何优化？我试图在另一个线程中向Bill提出类似的问题，但他似乎忽视了它。 - newtover

2

@Paul - 为了解决存在多行与每个组的最大值匹配的情况，并且您希望仅获取一行，您可以始终在ON子句逻辑中添加主键以打破平局...

SELECT t0.col3
  FROM table AS t0
  LEFT JOIN table AS t1
    ON t0.col1 = t1.col1
   AND t0.col2 = t1.col2
   AND (t1.col3, t1.pk) > (t0.col3, t0.pk)
 WHERE t1.col1 IS NULL
;

- Jon Armstrong - Xgc

2

这段代码可以更易读，如下所示：

SELECT t0.col3
FROM table AS t0
WHERE NOT EXISTS (SELECT 1 FROM table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3)

- wrschneider

@wrschneider：这样做会更易读，但在回答写作时，可能会慢得多。子查询支持是MySQL的相对后来者，并且最初性能较差。我希望今天两个查询都非常优化，但我不能说我一直在跟踪发展... - bobince

显示剩余9条评论

104

我总是遵循这个模式。给定这张表：

+------+------+
|    i |    j |
+------+------+
|    1 |   11 |
|    1 |   12 |
|    1 |   13 |
|    2 |   21 |
|    2 |   22 |
|    2 |   23 |
|    3 |   31 |
|    3 |   32 |
|    3 |   33 |
|    4 |   14 |
+------+------+

您可以获得这个结果：

+------+------+------------+
|    i |    j | row_number |
+------+------+------------+
|    1 |   11 |          1 |
|    1 |   12 |          2 |
|    1 |   13 |          3 |
|    2 |   21 |          1 |
|    2 |   22 |          2 |
|    2 |   23 |          3 |
|    3 |   31 |          1 |
|    3 |   32 |          2 |
|    3 |   33 |          3 |
|    4 |   14 |          1 |
+------+------+------------+

通过运行此查询，无需定义任何变量：

SELECT a.i, a.j, count(*) as row_number FROM test a
JOIN test b ON a.i = b.i AND a.j >= b.j
GROUP BY a.i, a.j

- Mosty Mostacho

1

如果列是VARCHAR或CHAR类型，你如何使用这个结构来处理它们？ - Tushar

@Tushar，运算符<、>、<=、>=按字母顺序处理CHAR和VARCHAR数据类型；我认为这正是你要找的。 - alex

我能加上一个条件 where row_number <= 2 吗? 如何加？ - gevaraweb

1

@AlmazVildanov，你应该能够简单地将此查询用作子查询来过滤掉“row_numbers <= 2”。非常感谢这个完美的答案，Mosty！ - Zax

74

SELECT 
    @i:=@i+1 AS iterator, 
    t.*
FROM 
    tablename AS t,
    (SELECT @i:=0) AS foo

- Peter Johnson

我猜 (SELECT @i:=0) AS foo 应该是FROM语句中第一个表，特别是如果其他表使用子查询。 - andig

1

你为什么需要“.. as foo”？ - Tom Chiverton

@TomChiverton 如果缺失，您将收到以下错误代码："错误代码：1248。每个派生表都必须有自己的别名"。 - ExStackChanger

3

此处的等级分配完全未定义，甚至无法回答这个问题。 - jberryman

53

从 MySQL 8.0.0 版本开始，您可以原生地使用窗口函数。

1.4 MySQL 8.0的新特性:

窗口函数。

MySQL现在支持窗口函数，对于来自查询的每一行，它们使用与该行相关的行执行计算。这些包括RANK()、LAG()和NTILE()等函数。此外，现有的几个聚合函数现在也可以用作窗口函数；例如，SUM()和AVG()。

ROW_NUMBER() over_clause :

返回其分区内当前行的编号。行编号范围从1到分区行数。

ORDER BY会影响编号行的顺序。如果没有指定ORDER BY，则行编号是不确定的。

演示：

CREATE TABLE Table1(
  id INT AUTO_INCREMENT PRIMARY KEY, col1 INT,col2 INT, col3 TEXT);

INSERT INTO Table1(col1, col2, col3)
VALUES (1,1,'a'),(1,1,'b'),(1,1,'c'),
       (2,1,'x'),(2,1,'y'),(2,2,'z');

SELECT 
    col1, col2,col3,
    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1;

DBFiddle演示

- Lukasz Szozda

29

看看这篇文章，它展示了如何在MySQL中模拟带有分区的SQL ROW_NUMBER()。我在WordPress实现中遇到了同样的情况。我需要ROW_NUMBER()，但它不存在。

http://www.explodybits.com/2011/11/mysql-row-number/

文章中的示例是使用一个字段进行分区。要按其他字段进行分区，您可以像这样操作：

  SELECT  @row_num := IF(@prev_value=concat_ws('',t.col1,t.col2),@row_num+1,1) AS RowNumber
         ,t.col1 
         ,t.col2
         ,t.Col3
         ,t.col4
         ,@prev_value := concat_ws('',t.col1,t.col2)
    FROM table1 t,
         (SELECT @row_num := 1) x,
         (SELECT @prev_value := '') y
   ORDER BY t.col1,t.col2,t.col3,t.col4

使用concat_ws处理null值。我测试了一个int、date和varchar字段。希望这可以帮助你。查看本文，它将分解并解释此查询。

- birch

1

太棒了。这实际上完成了分区。非常方便。 - Stuart Watt

2

与自连接相比，这种方法更加高效，但是逻辑上存在问题，必须在计算行号之前进行排序，同时concat也不是必要的。

SELECT  @row_num := IF(@prev_col1=t.col1 AND @prev_col2=t.col2), @row_num+1, 1) AS RowNumber
         ,t.col1 
         ,t.col2
         ,t.col3
         ,t.col4
         ,@prev_col1 := t.col1
         ,@prev_col2 := t.col2
    FROM (SELECT * FROM table1 ORDER BY col1, col2, col3) t,
         (SELECT @row_num := 1, @prev_col1 := '', @prev_col2 := '') var

- Kenneth Xu

如果您需要将此放入子查询中，则添加“limit 18446744073709551615”以强制使用“order by”子句。 - xmedeko

1

使用空字符串 '' 的 concat_ws 是危险的：concat_ws('',12,3) = concat_ws('',1,23)。最好使用一些分隔符 '_' 或使用 @Kenneth Xu 的解决方案。 - xmedeko

1

op的链接已经失效了；链接的存档在这里。 - sam-6174

19

我也会投票支持Mosty Mostacho的解决方案，只需要稍微修改他的查询代码：

SELECT a.i, a.j, (
    SELECT count(*) from test b where a.j >= b.j AND a.i = b.i
) AS row_number FROM test a

将会得到相同的结果：

+------+------+------------+
|    i |    j | row_number |
+------+------+------------+
|    1 |   11 |          1 |
|    1 |   12 |          2 |
|    1 |   13 |          3 |
|    2 |   21 |          1 |
|    2 |   22 |          2 |
|    2 |   23 |          3 |
|    3 |   31 |          1 |
|    3 |   32 |          2 |
|    3 |   33 |          3 |
|    4 |   14 |          1 |
+------+------+------------+

对于这个表格：

+------+------+
|    i |    j |
+------+------+
|    1 |   11 |
|    1 |   12 |
|    1 |   13 |
|    2 |   21 |
|    2 |   22 |
|    2 |   23 |
|    3 |   31 |
|    3 |   32 |
|    3 |   33 |
|    4 |   14 |
+------+------+

唯一的区别是查询不使用JOIN和GROUP BY，而是依靠嵌套选择语句。

- abcdn

1

这是否应该更好？它们两个看起来都可能是二次的，但我不确定如何解释 EXPLAIN 的输出。 - jberryman

事实上，在MySQL中，嵌套查询并不是很好优化的，因此这个答案只是为了演示一种查询技术。我认为，基于变量的示例对于大多数实际情况效果更好。 - abcdn

1

我并不认为任何基于变量的答案实际上都在使用已定义的行为... - jberryman

抱歉，我不确定你所说的“定义行为”是什么意思。你是指它对你无效，还是担心它没有记录下来？ - abcdn

1

“未定义的行为”表示没有记录下其正常工作和/或无法保证其正常工作。请参见此页面上注释中的文档引用和链接。它可能返回一个人（不合理地）想要、猜测、假设或幻想的结果。对于某些版本的实现，使用CASE递增和变量的某些查询表达式已经被Percona的程序员通过查看代码证明可以正常工作。但这在任何发布版本中都可能发生改变。 - philipxy

12

I would define a function:

delimiter $$
DROP FUNCTION IF EXISTS `getFakeId`$$
CREATE FUNCTION `getFakeId`() RETURNS int(11)
    DETERMINISTIC
begin
return if(@fakeId, @fakeId:=@fakeId+1, @fakeId:=1);
end$$

那么我可以这样做：

然后我可以：

select getFakeId() as id, t.* from table t, (select @fakeId:=0) as t2;

现在你没有子查询，因为视图中不能有子查询。

- Quincy

有一个限制：如果您多次执行查询，则会为相同的结果集获得越来越多的虚假ID。 - Stephan Richter

每次想运行查询时，您可以发送“set @fakeId =0;”，这不是最优解，但可以工作。 - jmpeace

如果删除DETERMINISTIC，就会出现一个非常奇怪的问题。然后在使用order by时，fakeId是不正确的。为什么会这样呢？ - Chris Muench

10

在 MySQL 中查询行号

set @row_number=0;
select (@row_number := @row_number +1) as num,id,name from sbs

- user5528503

这可以用于UPDATE查询吗？我尝试了，但是出现了“数据截断列…”的错误。 - Diego

1

如果有人想在UPDATE中使用它，必须将其用作子查询才能正常工作。UPDATE <table> SET <field> = (SELECT @row_number := @row_number +1) ORDER BY <your order column>; 排序列确定行的值排序。 - Diego

9

MySQL 中没有像 rownum 或 row_num() 这样的函数，但可以通过以下方式实现相同的功能：

select 
      @s:=@s+1 serial_no, 
      tbl.* 
from my_table tbl, (select @s:=0) as s;

- Md. Kamruzzaman

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- OMG Ponies · Accepted Answer

246

MySQL中没有排名功能，最接近的方法是使用变量：

SELECT t.*, 
       @rownum := @rownum + 1 AS rank
  FROM YOUR_TABLE t, 
       (SELECT @rownum := 0) r

那么在我的情况下该怎么办？我需要两个变量，一个用于col1，另一个用于col2？当col1更改时，col2需要进行某种重置..?

是的。如果使用Oracle，您可以使用LEAD函数查看下一个值。幸运的是，Quassnoi涵盖了您需要在MySQL中实现的逻辑。

- OMG Ponies

1

哦......那在我的情况下该怎么做呢？我需要两个变量，一个用于 col1，另一个用于 col2。当 col1 变化时，col2 需要以某种方式重置吗？ - Paul

14

在同一语句中给用户定义的变量赋值并读取它们是不可靠的。在这里有相关文档：http://dev.mysql.com/doc/refman/5.0/en/user-variables.html：“通常情况下，您不应该在同一语句中对用户变量进行赋值并读取该值。您可能会得到所期望的结果，但这并不保证。涉及用户变量的表达式的求值顺序是未定义的，并且可能会根据给定语句中包含的元素而更改。” - Roland Bouman

3

正如Roland所指出的那样，这似乎是未定义的行为。例如，对于我尝试过的一个表格，这将会给出完全不正确的结果：

SELECT @row_num:=@row_num+1 AS row_number, t.id FROM (SELECT * FROM table1 WHERE col = 264 ORDER BY id) t, (SELECT @row_num:=0) var;

。 - jberryman

1

这个能在MySQL上运行吗？我运行时遇到了语法错误... - Xin Niu

1

对于MySQL 8+，请使用内置的row_number()解决方案，而不是这个：https://dev59.com/enI-5IYBdhLWcg3wW3Cp#46753800 - TylerH

显示剩余4条评论