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

我会定义一个函数:

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;

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

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

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

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

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

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

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

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

希望这对你有帮助!

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

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

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