MySQL 中的 ROW_NUMBER()

发布于 2024-08-14 02:09:31 字数 329 浏览 8 评论 0原文

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

原文

Is there a nice way in MySQL to replicate the SQL Server function 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 intRow to 1 to get a single row with the highest col3 for each (col1, col2) pair.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

动次打次papapa 2024-08-21 02:09:31

MySQL 5.7 或更低版本中没有排名功能。（MySQL v8.0+ 支持此功能，请参阅@LukaszSzozda 的回答

）可以得到的是使用一个变量：

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

那么对于我的情况来说，这会如何呢？我需要两个变量，col1 和 col2 各一个？当 col1 更改时，Col2 需要以某种方式重置..？

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

There is no ranking functionality in MySQL 5.7 or below. (This is supported in MySQL v8.0+, see @LukaszSzozda's answer)

The closest you can get is to use a variable:

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..?

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.

回复收藏 0 原文

极致的悲 2024-08-21 02:09:31

我想要每个 (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，则此解决方案和大多数其他分组最大解决方案将返回多行。如果这是一个问题，您可能需要一些后处理。）

I want the row with the single highest col3 for each (col1, col2) pair.

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).

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.)

回复收藏 0 原文

洒一地阳光 2024-08-21 02:09:31

我总是遵循这种模式。给定此表：

+------+------+
|    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(*) row_number FROM test a
JOIN test b ON a.i = b.i AND a.j >= b.j
GROUP BY a.i, a.j

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(*) row_number FROM test a
JOIN test b ON a.i = b.i AND a.j >= b.j
GROUP BY a.i, a.j

回复收藏 0 原文

烟花易冷人易散 2024-08-21 02:09:31

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

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

回复收藏 0 原文

被你宠の有点坏 2024-08-21 02:09:31

从 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 演示

From MySQL 8.0.0 and above you could natively use windowed functions.

1.4 What Is New in MySQL 8.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().

ROW_NUMBER() over_clause :

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.

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;

DBFiddle Demo

回复收藏 0 原文

暖心男生 2024-08-21 02:09:31

查看这篇文章，它展示了如何在 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 对 3 个字段进行了测试。希望这有帮助。查看这篇文章，它分解了这个查询并对其进行了解释。

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.

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.

回复收藏 0 原文

ˉ厌 2024-08-21 02:09:31

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

I would also vote for Mosty Mostacho's solution with minor modification to his query code:

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.

回复收藏 0 原文

鹿童谣 2024-08-21 02:09:31

我会定义一个函数：

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;

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

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.

回复收藏 0 原文

夏天碎花小短裙 2024-08-21 02:09:31

在mysql中查询row_number

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

query for row_number in mysql

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

回复收藏 0 原文

吃素的狼 2024-08-21 02:09:31

MySQL 中没有像 rownum、row_num() 这样的函数，但解决方法如下：

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

There is no funtion like rownum, row_num() in MySQL but the way around is like below:

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

回复收藏 0 原文

新雨望断虹 2024-08-21 02:09:31

重要提示：请考虑升级到 MySQL 8+ 并使用已定义和记录的 ROW_NUMBER() 函数，并放弃与功能有限的旧版本 MySQL 相关的旧黑客

现在这是这些黑客之一：

答案在这里大多数/全部使用查询中变量似乎忽略了文档所说的事实（释义）：

不要依赖于按从上到下的顺序评估 SELECT 列表中的项目。不要在一个 SELECT 项中分配变量并在另一项中使用它们

因此，他们存在产生错误答案的风险，因为他们通常会执行“

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

如果这些自下而上进行评估，则行号将停止工作（不分区）

所以我们需要使用有保证执行顺序的东西。输入案例：

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 值，而不是上一个行的值）行的列值）。

以下是它们的组合方式：

第一个 WHEN 被评估。如果该行的 col 与前一行的 col 相同，则 @r 递增并从 CASE 返回。该返回值存储在@r 中。 MySQL 的一个功能是，赋值会将分配给 @r 的新值返回到结果行中。
对于结果集上的第一行，@prevcol 为 null（它在子查询中初始化为 null），因此该谓词为 false。每次 col 更改时，第一个谓词也会返回 false（当前行与前一行不同）。这会导致评估第二个 WHEN。
第二个 WHEN 谓词始终为 false，它的存在纯粹是为了向 @prevcol 分配新值。因为这一行的 col 与前一行的 col 不同（我们知道这一点，因为如果它相同，则将使用第一个 WHEN），因此我们必须分配新值以保留它以供下次测试。因为先进行赋值，然后将赋值结果与 null 进行比较，而与 null 等同的任何内容都是 false，因此该谓词始终为 false。但至少评估它做了保留该行的 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 表示“分区”，o 表示“分区” ocol 中的意思是“顺序” - 在一般形式中，我从变量名称中删除了“prev”以减少视觉混乱
(@pcolX := colX) = null 周围的括号很重要。如果没有它们，您会将 null 分配给 @pcolX 并且事情会停止工作
这是一个折衷方案，结果集也必须按分区列排序，以便与前一列进行比较才能计算出来。因此，您不能根据一列对行号进行排序，但将结果集排序到另一列您也许可以使用子查询来解决此问题，但我相信文档还指出，除非使用 LIMIT，否则可能会忽略子查询排序，这可能会影响性能
除了测试该方法是否有效之外，我还没有深入研究它，但是如果存在第二个 WHEN 中的谓词将被优化掉的风险（与 null 相比的任何内容都是 null/false 那么为什么还要运行分配）并且不执行，它也会停止。根据我的经验，这似乎不会发生，但如果可以合理地发生，我会很乐意接受评论并提出解决方案
将创建 @pcolX 的空值转换为列的实际类型可能是明智的，在创建 @pcolX 变量的子查询，即： select @pcol1 := CAST(null as INT), @pcol2 := CAST(null as DATE)

Important: Please consider upgrading to MySQL 8+ and use the defined and documented ROW_NUMBER() function, and ditch old hacks tied to a feature limited ancient version of MySQL

Now here's one of those hacks:

The answers here that use in-query variables mostly/all seem to ignore the fact that the documentation says (paraphrase):

Don't rely on items in the SELECT list being evaluated in order from top to bottom. Don't assign variables in one SELECT item and use them in another one

As such, there's a risk they will churn out the wrong answer, because they typically do a

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

If these are ever evaluated bottom up, the row number will stop working (no partitions)

So we need to use something with a guaranteed order of execution. Enter CASE WHEN:

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

As outline ld, order of assignment of prevcol is important - prevcol has to be compared to the current row's value before we assign it a value from the current row (otherwise it would be the current rows col value, not the previous row's col value).

Here's how this fits together:

The first WHEN is evaluated. If this row's col is the same as the previous row's col then @r is incremented and returned from the CASE. This return led values is stored in @r. It's a feature of MySQL that assignment returns the new value of what is assigned into @r into the result rows.
For the first row on the result set, @prevcol is null (it is initialised to null in the subquery) so this predicate is false. This first predicate also returns false every time col changes (current row is different to previous row). This causes the second WHEN to be evaluated.
The second WHEN predicate is always false, and it exists purely to assign a new value to @prevcol. Because this row's col is different to the previous row's col (we know this because if it were the same, the first WHEN would have been used), we have to assign the new value to keep it for testing next time. Because the assignment is made and then the result of the assignment is compared with null, and anything equated with null is false, this predicate is always false. But at least evaluating it did its job of keeping the value of col from this row, so it can be evaluated against the next row's col value
Because the second WHEN is false, it means in situations where the column we are partitioning by (col) has changed, it is the ELSE that gives a new value for @r, restarting the numbering from 1

We this get to a situation where this:

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

Has the general form:

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

Footnotes:

The p in pcol means "partition", the o in ocol means "order" - in the general form I dropped the "prev" from the variable name to reduce visual clutter
The brackets around (@pcolX := colX) = null are important. Without them you'll assign null to @pcolX and things stop working
It's a compromise that the result set has to be ordered by the partition columns too, for the previous column compare to work out. You can't thus have your rownumber ordered according to one column but your result set ordered to another You might be able to resolve this with subqueries but I believe the docs also state that subquery ordering may be ignored unless LIMIT is used and this could impact performance
I haven't delved into it beyond testing that the method works, but if there is a risk that the predicates in the second WHEN will be optimised away (anything compared to null is null/false so why bother running the assignment) and not executed, it also stops. This doesn't seem to happen in my experience but I'll gladly accept comments and propose solution if it could reasonably occur
It may be wise to cast the nulls that create @pcolX to the actual types of your columns, in the subquery that creates the @pcolX variables, viz: select @pcol1 := CAST(null as INT), @pcol2 := CAST(null as DATE)

回复收藏 0 原文

留一抹残留的笑 2024-08-21 02:09:31

我发现最有效的解决方案是使用这样的子查询：

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

PARTITION BY 列只需与“=”进行比较并用 AND 分隔。 ORDER BY 列将与“<”进行比较或“>”，并用 OR 分隔。

我发现这非常灵活，即使成本有点高。

The solution I found to work the best was using a subquery like this:

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

The PARTITION BY columns just get compared with '=' and separated by AND. The ORDER BY columns would be compared with '<' or '>', and separated by OR.

I've found this to be very flexible, even if it is a little bit costly.

回复收藏 0 原文

送舟行 2024-08-21 02:09:31

rownumber 功能无法被模仿。您可能会得到预期的结果，但在某个阶段您很可能会感到失望。
mysql 文档是这么说的：

对于其他语句，例如 SELECT，您可能会得到预期的结果，但这并不能保证。在下面的语句中，您可能会认为 MySQL 将首先评估 @a，然后再进行赋值：
选择@a，@a：=@a+1，...；
但是，涉及用户变量的表达式的求值顺序是未定义的。

问候，
乔治.

回复收藏 0 原文

不及他 2024-08-21 02:09:31

MariaDB 10.2 正在实现“窗口函数”，包括 RANK()、ROW_NUMBER() 和其他几个功能：

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

根据本月 Percona Live 上的演讲，它们得到了相当好的优化。

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

回复收藏 0 原文

染火枫林 2024-08-21 02:09:31

MySQL 从 8.0+ 版本开始支持 ROW_NUMBER()。

如果您使用 MySQL 8.0 或更高版本，请查看 ROW_NUMBER() 函数。
否则，您可以模拟 ROW_NUMBER() 函数。

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

对于旧版本，

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

MySQL has supported the ROW_NUMBER() since version 8.0+.

If you use MySQL 8.0 or later, check it out ROW_NUMBER() function.
Otherwise, you have emulate ROW_NUMBER() function.

The row_number() is a ranking function that returns a sequential number of a row, starting from 1 for the first row.

for older version,

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

回复收藏 0 原文

桃酥萝莉 2024-08-21 02:09:31

这允许在 MySQL 中实现 ROW_NUMBER() AND 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

This allows the same functionality that ROW_NUMBER() AND PARTITION BY provides to be achieved in MySQL

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

回复收藏 0 原文

眼波传意 2024-08-21 02:09:31

我没有看到任何涵盖“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”部分

 案例 @partitionBy_1 = 第 1 部分 AND @partitionBy_2 = 第 2 部分 [...] THEN @row_number:=@row_number+1 ELSE @row_number:=1 END AS i
    , @partitionBy_1:=part1 AS P1
    , @partitionBy_2:=part2 AS P2
    [...] 
从 （
    选择@row_number:=0,@partitionBy_1:=null,@partitionBy_2:=null[...]
) 作为 x

I don't see any simple answer covering the "PARTITION BY" part so here's mine :

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

The ORDER BY clause must reflect your ROW_NUMBER need. Thus there's already a clear limitation: you can't have several ROW_NUMBER "emulation" of this form at the same time.
The order of the "computed column" matters. If you have mysql compute those column in another order, it might not work.

In this simple example I only put one but you can have several "PARTITION BY" parts

    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

回复收藏 0 原文

我的黑色迷你裙 2024-08-21 02:09:31

这也可能是一个解决方案：

SET @row_number = 0;

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

This could also be a solution:

SET @row_number = 0;

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

回复收藏 0 原文

∞梦里开花 2024-08-21 02:09:31

如果您的查询包含 GROUP BY 语句，则使用交叉连接和逗号的解决方案将不起作用。对于这种情况，您可以使用子选择：

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

Solutions with cross join and comma won't work if your query has GROUP BY statement. For such cases you can use 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

回复收藏 0 原文

好多鱼好多余 2024-08-21 02:09:31

我认为你可以在这里使用 DENSE_RANK() 函数。
示例：

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

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

I think you can use DENSE_RANK() function here.
Example:

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

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

回复收藏 0 原文

酸甜透明夹心 2024-08-21 02:09:31

有点晚了，但也可能对寻找答案的人有所帮助...

在 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

A bit late but may also help to someone who looks for answers...

Between rows/row_number example - recursive query that may be used in any 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

回复收藏 0 原文

我爱人 2024-08-21 02:09:31

也有点晚了，但今天我有同样的需求，所以我在谷歌上搜索，最后在 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

3 意味着 MAKE_SET 的第一个参数我想要 SET 中的两个值 (3=1|2)。
当然，如果我们没有两个或更多列来构造组，我们可以消除 MAKE_SET 操作。结构完全相同。这对我来说是按要求工作的。非常感谢 Pinal Dave 的清晰演示。

Also a bit late but today I had the same need so I did search on Google and finally a simple general approach found here in Pinal Dave's article http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

I wanted to focus on Paul's original question (that was my problem as well) so I summarize my solution as a working example.

Beacuse we want to partition over two column I would create a SET variable during the iteration to identify if a new group was started.

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

The 3 means at the first parameter of MAKE_SET that I want both value in the SET (3=1|2).
Of course if we do not have two or more columns constructing the groups we can eliminate the MAKE_SET operation. The construction is exactly the same. This is working for me as required. Many thanks to Pinal Dave for his clear demonstration.

回复收藏 0 原文

烟酒忠诚 2024-08-21 02:09:31

这不是最强大的解决方案 - 但如果您只是想在只有几个不同值的字段上创建分区排名，那么使用具有所需数量的变量的逻辑时可能并不难处理。

过去这样的事情对我有用：

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

希望这是有意义的/有帮助！

This is not the most robust solution - but if you're just looking to create a partitioned rank on a field with only a few different values, it may not be unwieldily to use some case when logic with as many variables as you require.

Something like this has worked for me in the past:

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

Hope that makes sense / helps!

回复收藏 0 原文

倾｀听者〃 2024-08-21 02:09:31

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                    |

Still supporting MySQL 5.7.38 in 2023, and needing ROW_NUMBER() I ended up doing something like this:

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

Results:

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

回复收藏 0 原文

江南烟雨〆相思醉 2024-08-21 02:09:31

当我们有多个列时，这非常适合我创建 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

This Work perfectly for me to create RowNumber when we have more than one column. In this case two column.

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

回复收藏 0 原文

洛阳烟雨空心柳 2024-08-21 02:09:31

对于另一列的分区，一种方法是@abcdn 描述的方法。然而，它的性能较低。 我建议使用此代码，它不需要将表与其自身连接起来：
考虑同一张桌子。

你可以像这样进行分区：

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;

结果将是这样的：

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

for the partioning over anothe column one way is that described by @abcdn. However, it has a low performance. I propose use this code which does not require joining a table with itself:
Considee the same table.

you can get paritioning like this:

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;

the reult would be like this :

The advantage is we do not need to join table with itself

回复收藏 0 原文

~没有更多了~