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

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

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

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分隔。

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

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

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

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

SET @row_number = 0;

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