MySQL中带有Order By子句的排名函数 [英] Rank function in MySQL with Order By clause

问题描述

这(Oracle)SQL如何:

How could this (Oracle) SQL:

select a.*, rank() over (partition by a.field1 order by a.field2 desc) field_rank
from table_a a
order by a.field1, a.field2

被翻译成MySQL吗?

be translated into MySQL?

这个问题似乎很相似，但是在基本查询的末尾没有Order By .另外，按分区的字段排序是否重要?

This question seems to be similar but there is no Order By in the end of the base query. Also, does it matter that it is ordered by the fields of partition?

推荐答案

根据您提供的链接，其外观应如下所示:

According to the link you gave it should look like this:

SELECT    a.*,
( 
            CASE a.field1 
            WHEN @curType 
            THEN @curRow := @curRow + 1 
            ELSE @curRow := 1 AND @curType := a.field1 END
          ) + 1 AS rank
FROM      table_a a,
          (SELECT @curRow := 0, @curType := '') r
ORDER BY  a.field1, a.field2 desc;

根据您提供的链接中的示例，这里有2个小提琴，一个是oracle，一个是mySql.

Here are 2 fiddles, one for oracle and one for mySql based on the example from the link you gave:

1. oracle
2. Mysql

1. oracle
2. Mysql

这篇关于MySQL中带有Order By子句的排名函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！