ROW_NUMBER()在MySQL

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

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

那么在我的案例中这是如何起作用的呢?我需要两个变量，分别代表col1和col2?当col1改变时，Col2需要以某种方式重置…?

是的。如果它是Oracle，您可以使用LEAD函数在下一个值处达到峰值。值得庆幸的是，Quassnoi覆盖了你需要在MySQL中实现的逻辑。

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

这是一个groupwise最大，最常被问到的SQL问题之一(因为它看起来应该很简单，但实际上并非如此)。

我经常支持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;

"获取表中没有匹配col1、col2的其他行具有更高的col3的行。(你会注意到，如果不止一行具有相同的col1、col2、col3，那么这个和大多数其他分组最大值解将返回多行。如果这是一个问题，你可能需要一些后期处理。)

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

我会定义一个函数:

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;

现在你没有子查询，视图中没有子查询。

看看这篇文章，它展示了如何在MySQL中使用分区by模拟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对3个字段进行了测试。希望这能有所帮助。查看这篇文章，因为它分解了这个查询并解释了它。

我总是遵循这个模式。给定这个表格:

+------+------+
|    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

我发现最好的解决方案是使用这样的子查询:

SELECT
col1, col2,
(
SELECT COUNT(*)
FROM Table1
WHERE col1 = t1.col1
AND col2 = t1.col2
AND col3 > t1.col3
) AS intRow
FROM Table1 t1

分区BY列只是用'='进行比较，并用and分隔。ORDER BY列将与'<'或'>'进行比较，并以or分隔。

我发现这是非常灵活的，即使它有点昂贵。

有点晚了，但也可能对那些寻找答案的人有帮助……

Between rows/row_number示例-可以在任何SQL中使用的递归查询:

WITH data(row_num, some_val) AS
(
SELECT 1 row_num, 1 some_val FROM any_table --dual in Oracle
UNION ALL
SELECT row_num+1, some_val+row_num FROM data WHERE row_num < 20 -- any number
)
SELECT * FROM data
WHERE row_num BETWEEN 5 AND 10
/


ROW_NUM    SOME_VAL
-------------------
5           11
6           16
7           22
8           29
9           37
10          46

这允许在MySQL中实现ROW_NUMBER()和PARTITION BY提供的相同功能

SELECT  @row_num := IF(@prev_value=GENDER,@row_num+1,1) AS RowNumber
FirstName,
Age,
Gender,
@prev_value := GENDER
FROM Person,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY Gender, Age DESC

也有点晚了，但今天我有同样的需要，所以我在谷歌上搜索了一下，最后在Pinal Dave的文章http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/中找到了一个简单的一般方法

我想把重点放在Paul最初的问题上(这也是我的问题)，所以我把我的解决方案总结为一个工作示例。

因为我们想要在两个列上进行分区，我将在迭代期间创建一个SET变量来识别是否启动了一个新组。

SELECT col1, col2, col3 FROM (
SELECT col1, col2, col3,
@n := CASE WHEN @v = MAKE_SET(3, col1, col2)
THEN @n + 1 -- if we are in the same group
ELSE 1 -- next group starts so we reset the counter
END AS row_number,
@v := MAKE_SET(3, col1, col2) -- we store the current value for next iteration
FROM Table1, (SELECT @n := 0, @v := NULL) r -- helper table for iteration with startup values
ORDER BY col1, col2, col3 DESC -- because we want the row with maximum value
) x WHERE row_number = 1 -- and here we select exactly the wanted row from each group

不能模仿rownumber功能。你可能会得到你期望的结果，但你很可能会在某个阶段失望。 下面是mysql文档说的:

对于其他语句，例如SELECT，您可能会得到您期望的结果，但这并不保证。在下面的语句中，你可能认为MySQL会先计算@a，然后再赋值: SELECT @a， @a:=@a+1，… 但是，涉及用户变量的表达式的求值顺序是未定义的。< / p >

我没有看到任何简单的答案涵盖“PARTITION BY”部分，所以我的答案是:

SELECT
*
FROM (
select
CASE WHEN @partitionBy_1 = l THEN @row_number:=@row_number+1 ELSE @row_number:=1 END AS i
, @partitionBy_1:=l AS p
, t.*
from (
select @row_number:=0,@partitionBy_1:=null
) as x
cross join (
select 1 as n, 'a' as l
union all
select 1 as n, 'b' as l
union all
select 2 as n, 'b' as l
union all
select 2 as n, 'a' as l
union all
select 3 as n, 'a' as l
union all
select 3 as n, 'b' as l
) as t
ORDER BY l, n
) AS X
where i > 1

• ORDER BY子句必须反映您的ROW_NUMBER需求。因此，这里有一个明显的限制:您不能同时对该表单进行多个ROW_NUMBER“模拟”。
• “计算列”的顺序重要的。如果你让mysql以另一种顺序计算这些列，它可能不起作用。
• 在这个简单的例子中，我只放了一个，但你可以有几个“PARTITION BY”部分

      CASE WHEN @partitionBy_1 = part1 AND @partitionBy_2 = part2 [...] THEN @row_number:=@row_number+1 ELSE @row_number:=1 END AS i
  , @partitionBy_1:=part1 AS P1
  , @partitionBy_2:=part2 AS P2
  [...]
  FROM (
  SELECT @row_number:=0,@partitionBy_1:=null,@partitionBy_2:=null[...]
  ) as x
  

我也会投票给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，而是依赖于嵌套选择。

查询mysql中的row_number

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

MariaDB 10.2实现了“窗口函数”，包括RANK()， ROW_NUMBER()和其他一些东西:

https://mariadb.com/kb/en/mariadb/window-functions/

根据本月在Percona Live上的一次演讲，它们得到了合理的优化。

语法与问题中的代码相同。

在MySQL中没有像rownum， row_num()这样的函数，但方法如下:

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

当我们有一个以上的列时，这个工作完美地为我创建RowNumber。这里是两列。

SELECT @row_num := IF(@prev_value= concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`), @row_num+1, 1) AS RowNumber,
`Fk_Business_Unit_Code`,
`NetIQ_Job_Code`,
`Supervisor_Name`,
@prev_value := concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`)
FROM (SELECT DISTINCT `Fk_Business_Unit_Code`,`NetIQ_Job_Code`,`Supervisor_Name`
FROM Employee
ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`, `Supervisor_Name` DESC) z,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`,`Supervisor_Name` DESC

在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 Demo

这也可以是一个解决方案:

SET @row_number = 0;


SELECT
(@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
employees

重要提示:请考虑升级到MySQL 8+，并使用已定义和文档化的ROW_NUMBER()函数，并抛弃老黑客绑定到一个功能有限的古老版本的MySQL

下面是其中一个小技巧:

这里大部分/全部使用查询变量的答案似乎忽略了一个事实，即文档说(转述):

不要依赖于SELECT列表中的项按从上到下的顺序求值。不要在一个SELECT项中分配变量，而在另一个SELECT项中使用它们

因此，他们有可能会得出错误的答案，因为他们通常会做一个

select
(row number variable that uses partition variable),
(assign partition variable)

如果从下向上计算，行号将停止工作(没有分区)

所以我们需要使用一些有执行顺序保证的东西。输入CASE当:

SELECT
t.*,
@r := CASE
WHEN col = @prevcol THEN @r + 1
WHEN (@prevcol := col) = null THEN null
ELSE 1 END AS rn
FROM
t,
(SELECT @r := 0, @prevcol := null) x
ORDER BY col

作为大纲ld, prevcol的赋值顺序很重要——在我们从当前行为其赋值之前，必须将prevcol与当前行的值进行比较(否则它将是当前行的col值，而不是前一行的col值)。

以下是它们之间的联系:

• 计算第一个WHEN。如果这一行的col与前一行的col相同，则@r将递增并从CASE返回。返回的led值存储在@r中。这是MySQL的一个特性，assignment将赋值到@r的新值返回到结果行中。

• 对于结果集中的第一行，@prevcol为空(它在子查询中初始化为空)，因此该谓词为假。第一个谓词也在每次col改变时返回false(当前行与前一行不同)。这将导致第二个WHEN被计算。

• 第二个WHEN谓词总是false，它的存在纯粹是为了给@prevcol赋一个新值。因为这一行的col与前一行的col不同(我们知道这一点，因为如果它们相同，第一个WHEN将被使用)，我们必须分配新值以保留它以供下次测试。因为做了赋值，然后赋值的结果与null进行比较，任何等于null的值都是假的，所以这个谓词总是假的。但是至少求值的时候保留了这一行的col值，所以它可以根据下一行的col值求值

• 因为第二个WHEN为false，这意味着在我们用(col)进行分区的列已经改变的情况下，ELSE为@r提供了一个新值，从1重新开始编号

我们会遇到这样的情况:

SELECT
t.*,
ROW_NUMBER() OVER(PARTITION BY pcol1, pcol2, ... pcolX ORDER BY ocol1, ocol2, ... ocolX) rn
FROM
t

具有一般形式:

SELECT
t.*,
@r := CASE
WHEN col1 = @pcol1 AND col2 = @pcol2 AND ... AND colX = @pcolX THEN @r + 1
WHEN (@pcol1 := pcol1) = null OR (@pcol2 := col2) = null OR ... OR (@pcolX := colX) = null THEN null
ELSE 1
END AS rn
FROM
t,
(SELECT @r := 0, @pcol1 := null, @pcol2 := null, ..., @pcolX := null) x
ORDER BY pcol1, pcol2, ..., pcolX, ocol1, ocol2, ..., ocolX

脚注:

• pcol中的p表示“分区”，ocol中的o表示“顺序”——在一般形式下，我从变量名中去掉了“prev”，以减少视觉上的混乱

• (@pcolX := colX) = null周围的括号很重要。如果没有它们，你将把null赋值给@pcolX，事情就停止工作了

• 这是一种折衷，结果集也必须按分区列排序，以便计算出前一列的比较。因此，你不能让你的行号按照一列排序，但你的结果集按照另一列排序。你可能可以用子查询解决这个问题，但我相信文档也声明，除非使用LIMIT，否则子查询顺序可能被忽略，这可能会影响性能

• 除了测试该方法的工作之外，我还没有深入研究它，但如果存在第二个WHEN中的谓词将被优化的风险(与null相比的任何东西都是null/false，所以为什么要麻烦运行赋值)而不执行，它也会停止。在我的经验中，这似乎没有发生过，但我很乐意接受评论，并提出解决方案，如果它可以合理地发生

• 在创建@pcolX变量(即:select @pcol1 := CAST(null as INT), @pcol2 := CAST(null as DATE))的子查询中，将创建@pcolX的空值强制转换为列的实际类型可能是明智的

MySQL已经支持ROW_NUMBER()自版本8.0 +。

row_number()是一个排序函数，返回一行的顺序编号，第一行从1开始。

对于旧版本，

SELECT t.*,
@rowid := @rowid + 1 AS ROWID
FROM TABLE t,
(SELECT @rowid := 0) dummy;

这并不是最健壮的解决方案——但是如果您只是想在一个只有几个不同值的字段上创建一个分区的秩，当逻辑上有许多变量时，使用某些情况可能并不笨拙。

这样的方法在过去对我很有效:

SELECT t.*,
CASE WHEN <partition_field> = @rownum1 := @rownum1 + 1
WHEN <partition_field> = @rownum2 := @rownum2 + 1
...
END AS rank
FROM YOUR_TABLE t,
(SELECT @rownum1 := 0) r1, (SELECT @rownum2 := 0) r2
ORDER BY <rank_order_by_field>
;

希望这对你有帮助!

如果你的查询有GROUP BY语句，使用交叉连接和逗号的解决方案将不起作用。对于这种情况，您可以使用subselect:

SELECT (@row_number := @row_number + 1) AS rowNumber, res.*
FROM
(
SELECT SUM(r.amount)
FROM Results r
WHERE username = 1
GROUP BY r.amount
) res
CROSS JOIN (SELECT @row_number := 0) AS dummy

我认为你可以在这里使用DENSE_RANK()函数。 例子:< / p >

select `score`, DENSE_RANK() OVER( ORDER BY score desc ) as `rank` from Scores;

https://www.mysqltutorial.org/mysql-window-functions/mysql-dense_rank-function/

MySQL自版本8以来，支持ROW_NUMBER()，所以你可以像在SQL Server中使用一样使用下面的查询

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

我还在Maria DB 10.4.21中测试了它。在那里也同样有效。

对于另一个列的划分，一种方法是由@abcdn描述的。但是，它的性能很低。我建议使用这段代码，它不需要连接一个表本身: 考虑同一张桌子。
< / p >

你可以得到这样的分区:

set @row_num := 0;
set @j:= 0;


select IF(j= @j, @row_num := @row_num + 1, @row_num := 1) as row_num,
i, @j:= j as j
from tbl fh
order by j, i;

则结果如下所示:
< / p >

这样做的好处是我们不需要连接表本身