logo标签列表

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的单行。

对于一个简单的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个回答
1
有些晚了，但可能对寻找答案的人有所帮助...
在任何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
2
抱歉，据我所知，MySQL不支持常用表达式。 - Álvaro González
它现在不适用于旧版的MySQL，因为MySQL 8仅支持CTE和窗口函数，所以这个答案并没有实际意义。@ÁlvaroGonzález - Raymond Nijland
1
也许有点晚了，但今天我有同样的需求，所以我在谷歌上搜索，并最终在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

在MAKE_SET的第一个参数中，3表示我想要SET中的两个值（3=1|2）。 当然，如果我们没有两列或更多列来构建分组，我们可以省略MAKE_SET操作。构建方式完全相同。这对我来说正好符合要求。非常感谢Pinal Dave清晰的演示。
1
请注意，子查询中的ORDER BY可能会被忽略（请参阅https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/）。建议的解决方法是向子查询添加`LIMIT 18446744073709551615`，这将强制进行排序。但是，这可能会导致性能问题，并且对于真正巨大的表格无效。 - pnomolos
0

这不是最健壮的解决方案 - 但如果您只想在具有少量不同值的字段上创建分区排名，则使用尽可能多的变量使用一些 case when 逻辑可能不会很难处理。

像这样的东西过去对我有用：

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

希望这有意义/有所帮助！

0

在2023年仍然支持MySQL 5.7.38，并且需要ROW_NUMBER()，最后我做了类似这样的事情：

drop temporary table t1

create temporary table t1 (
    USER_ID VARCHAR(50),
    PRIORITY INT
)

insert into t1 (USER_ID, PRIORITY ) 
values 
('qqq',300),
('qqq',572),
('qqq',574),
('qqq',630),
('qqq',640),
('qqq',650),
('yyy',300),
('yyy',574),
('yyy',574),
('yyy',630),
('yyy',640),
('yyy',650)

    
 SELECT *,
    @row_number := IF(@prev_userid = USER_ID, @row_number + 1, 1) AS ROWNUM,
    @prev_userid := USER_ID
FROM t1
CROSS JOIN (SELECT @row_number := 0, @prev_userid := '') AS vars
ORDER BY USER_ID, PRIORITY

结果：

|USER_ID|PRIORITY|@row_number := 0|@prev_userid := ''|ROWNUM|@prev_userid := USER_ID|
|-------|--------|----------------|------------------|------|-----------------------|
|qqq    |300     |0               |                  |1     |qqq                    |
|qqq    |572     |0               |                  |2     |qqq                    |
|qqq    |574     |0               |                  |3     |qqq                    |
|qqq    |630     |0               |                  |4     |qqq                    |
|qqq    |640     |0               |                  |5     |qqq                    |
|qqq    |650     |0               |                  |6     |qqq                    |
|yyy    |300     |0               |                  |1     |yyy                    |
|yyy    |574     |0               |                  |2     |yyy                    |
|yyy    |574     |0               |                  |3     |yyy                    |
|yyy    |630     |0               |                  |4     |yyy                    |
|yyy    |640     |0               |                  |5     |yyy                    |
|yyy    |650     |0               |                  |6     |yyy                    |
-1

这对我来说非常完美，可以在有多个列的情况下创建行号。 在这种情况下是两列。

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

自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中测试过它。它在那里也可以工作。

-1

对于基于另一列的分区，一种方法是由@abcdn描述的。然而，它的性能较低。我建议使用这段代码，它不需要将表与自身连接： 考虑相同的表格。
enter image description here

您可以像这样获得分区：

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;

结果会像这样：
在此输入图片描述

优点是我们不需要将表与自身连接

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接