在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最高的单行。


当前回答

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

其他回答

如果查询有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

这允许在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

我会定义一个函数:

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;

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

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