重要提示:请考虑升级到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值)。

以下是它们之间的联系:

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

我们会遇到这样的情况:

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

脚注:

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)

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

我认为你可以在这里使用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/

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中测试了它。在那里也同样有效。

也有点晚了，但今天我有同样的需要，所以我在谷歌上搜索了一下，最后在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的清晰演示。

我没有看到任何简单的答案涵盖“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

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