在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中没有排名功能。最接近的方法是使用变量:

SELECT t.*, 
       @rownum := @rownum + 1 AS rank
  FROM YOUR_TABLE t, 
       (SELECT @rownum := 0) r

那么在我的案例中这是如何起作用的呢?我需要两个变量,分别代表col1和col2?当col1改变时,Col2需要以某种方式重置…?

是的。如果它是Oracle,您可以使用LEAD函数在下一个值处达到峰值。值得庆幸的是,Quassnoi涵盖了在MySQL中需要实现的逻辑。

其他回答

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

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

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

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

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

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

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

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

行号功能不能被模仿。你可能会得到你期望的结果,但你很可能会在某个阶段失望。 下面是mysql文档说的:

对于其他语句,例如SELECT,您可能会得到您期望的结果,但这并不保证。在下面的语句中,你可能认为MySQL会先计算@a,然后再赋值: SELECT @a, @a:=@a+1,… 但是,涉及用户变量的表达式的求值顺序是未定义的。

问候, 格奥尔基。

我想要每对(col1, col2)的col3最高的行。

这是一个分组最大值,是最常被问到的SQL问题之一(因为它看起来应该很简单,但实际上并非如此)。

我经常支持null-self-join:

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

"获取表中没有匹配col1、col2的其他行具有更高的col3的行。(你会注意到,如果不止一行具有相同的col1、col2、col3,那么这个和大多数其他分组最大值解将返回多行。如果这是一个问题,你可能需要一些后期处理。)