在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中使用分区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个字段进行了测试。希望这能有所帮助。查看这篇文章,因为它分解了这个查询并解释了它。

其他回答

如果查询有GROUP BY语句,使用交叉连接和逗号的解决方案将不起作用。对于这种情况,您可以使用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

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

对于另一个列的划分,一种方法是由@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;

结果是这样的:

这样做的好处是我们不需要连接表本身

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

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

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