MySQL 中的 ROW_NUMBER()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1895110/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ROW_NUMBER() in MySQL
提问by Paul
Is there a nice way in MySQL to replicate the SQL Server function ROW_NUMBER()?
MySQL 中是否有复制 SQL Server 功能的好方法ROW_NUMBER()？
For example:
例如：
SELECT
col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1
Then I could, for example, add a condition to limit intRowto 1 to get a single row with the highest col3for each (col1, col2)pair.
然后我可以，例如，添加一个条件限制intRow为 1 以获得col3每(col1, col2)对最高的单行。
采纳答案by bobince
I want the row with the single highest col3 for each (col1, col2) pair.
我想要每个 (col1, col2) 对具有单个最高 col3 的行。
That's a groupwise maximum, one of the most commonly-asked SQL questions (since it seems like it should be easy, but actually it kind of isn't).
这是一个groupwise maximum，最常问到的 SQL 问题之一（因为它看起来应该很容易，但实际上并不是这样）。
I often plump for a null-self-join:
我经常喜欢空自连接：
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;
“Get the rows in the table for which no other row with matching col1,col2 has a higher col3.” (You will notice this and most other groupwise-maximum solutions will return multiple rows if more than one row has the same col1,col2,col3. If that's a problem you may need some post-processing.)
“获取表中没有匹配 col1,col2 的其他行具有更高 col3 的行。” （您会注意到这一点，如果不止一行具有相同的 col1、col2、col3，大多数其他分组最大解决方案将返回多行。如果这是一个问题，您可能需要一些后处理。）
回答by OMG Ponies
There is no ranking functionality in MySQL. The closest you can get is to use a variable:
MySQL 中没有排名功能。你能得到的最接近的是使用一个变量：
SELECT t.*,
@rownum := @rownum + 1 AS rank
FROM YOUR_TABLE t,
(SELECT @rownum := 0) r
so how would that work in my case? I'd need two variables, one for each of col1 and col2? Col2 would need resetting somehow when col1 changed..?
那么在我的情况下这将如何工作？我需要两个变量，col1 和 col2 各一个？当 col1 改变时，col2 需要以某种方式重置..？
Yes. If it were Oracle, you could use the LEAD function to peak at the next value. Thankfully, Quassnoi covers the logic for what you need to implement in MySQL.
是的。如果是 Oracle，您可以使用 LEAD 函数在下一个值处达到峰值。幸运的是，Quassnoi 涵盖了您需要在 MySQL 中实现的逻辑。
回答by Mosty Mostacho
I always end up following this pattern. Given this table:
我最终总是遵循这种模式。鉴于此表：
+------+------+
| i | j |
+------+------+
| 1 | 11 |
| 1 | 12 |
| 1 | 13 |
| 2 | 21 |
| 2 | 22 |
| 2 | 23 |
| 3 | 31 |
| 3 | 32 |
| 3 | 33 |
| 4 | 14 |
+------+------+
You can get this result:
你可以得到这个结果：
+------+------+------------+
| 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 |
+------+------+------------+
By running this query, which doesn't need any variable defined:
通过运行这个不需要定义任何变量的查询：
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
Hope that helps!
希望有帮助！
回答by Peter Johnson
SELECT
@i:=@i+1 AS iterator,
t.*
FROM
tablename AS t,
(SELECT @i:=0) AS foo
回答by birch
Check out this Article, it shows how to mimic SQL ROW_NUMBER() with a partition by in MySQL. I ran into this very same scenario in a WordPress Implementation. I needed ROW_NUMBER() and it wasn't there.
查看这篇文章，它展示了如何在 MySQL 中使用分区来模拟 SQL ROW_NUMBER()。我在 WordPress 实现中遇到了同样的场景。我需要 ROW_NUMBER() 但它不存在。
http://www.explodybits.com/2011/11/mysql-row-number/
http://www.explodybits.com/2011/11/mysql-row-number/
The example in the article is using a single partition by field. To partition by additional fields you could do something like this:
文章中的示例是使用按字段进行的单个分区。要按附加字段进行分区，您可以执行以下操作：
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
Using concat_ws handles null's. I tested this against 3 fields using an int, date, and varchar. Hope this helps. Check out the article as it breaks this query down and explains it.
使用 concat_ws 处理空值。我使用 int、date 和 varchar 对 3 个字段进行了测试。希望这可以帮助。查看文章，因为它分解了此查询并对其进行了解释。
回答by Lukasz Szozda
From MySQL 8.0.0and above you could natively use windowed functions.
从MySQL 8.0.0上面开始，您可以本机使用窗口函数。
Window functions.
MySQL now supports window functions that, for each row from a query, perform a calculation using rows related to that row. These include functions such as RANK(), LAG(), and NTILE(). In addition, several existing aggregate functions now can be used as window functions; for example, SUM() and AVG().
窗口函数。
MySQL 现在支持窗口函数，对于查询中的每一行，使用与该行相关的行执行计算。其中包括 RANK()、LAG() 和 NTILE() 等函数。此外，几个现有的聚合函数现在可以用作窗口函数；例如，SUM() 和 AVG()。
Returns the number of the current row within its partition. Rows numbers range from 1 to the number of partition rows.
ORDER BY affects the order in which rows are numbered. Without ORDER BY, row numbering is indeterminate.
返回其分区内当前行的编号。行数范围从 1 到分区行数。
ORDER BY 影响行编号的顺序。如果没有 ORDER BY，行编号是不确定的。
Demo:
演示：
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;
回答by abcdn
I would also vote for Mosty Mostacho's solution with minor modification to his query code:
我也会投票支持 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
Which will give the same result:
这将给出相同的结果：
+------+------+------------+
| 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 |
+------+------+------------+
for the table:
对于表：
+------+------+
| i | j |
+------+------+
| 1 | 11 |
| 1 | 12 |
| 1 | 13 |
| 2 | 21 |
| 2 | 22 |
| 2 | 23 |
| 3 | 31 |
| 3 | 32 |
| 3 | 33 |
| 4 | 14 |
+------+------+
With the only difference that the query doesn't use JOIN and GROUP BY, relying on nested select instead.
唯一的区别是查询不使用 JOIN 和 GROUP BY，而是依靠嵌套选择。
回答by Quincy
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$$
then I could do:
那么我可以这样做：
select getFakeId() as id, t.* from table t, (select @fakeId:=0) as t2;
Now you don't have a subquery, which you can't have in views.
现在您没有子查询，而您不能在视图中使用该子查询。
回答by user5528503
query for row_number in mysql
在mysql中查询row_number
set @row_number=0;
select (@row_number := @row_number +1) as num,id,name from sbs
回答by Md. Kamruzzaman
There is no funtion like rownum, row_num()in MySQL but the way around is like below:
在 MySQL 中没有类似rownum, 的功能，row_num()但方法如下：
select
@s:=@s+1 serial_no,
tbl.*
from my_table tbl, (select @s:=0) as s;
