MySQL中的ROW_NUMBER() [英] ROW_NUMBER() in MySQL
问题描述
MySQL中是否有复制SQL Server函数ROW_NUMBER()
的好方法?
Is there a nice way in MySQL to replicate the SQL Server function 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
的单行.
Then I could, for example, add a condition to limit intRow
to 1 to get a single row with the highest col3
for each (col1, col2)
pair.
推荐答案
我想要每对(col1,col2)对具有最高col3的行.
I want the row with the single highest col3 for each (col1, col2) pair.
这是分组最大值,其中之一最常见的SQL问题(因为看起来应该很容易,但实际上并非如此).
That's a groupwise maximum, one of the most commonly-asked SQL questions (since it seems like it should be easy, but actually it kind of isn't).
我经常为null-self-join充实:
I often plump for a 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,则大多数其他按组最大的解决方案将返回多行.如果这是一个问题,则可能需要进行一些后期处理.)
"Get the rows in the table for which no other row with matching col1,col2 has a higher col3." (You will notice this and most other groupwise-maximum solutions will return multiple rows if more than one row has the same col1,col2,col3. If that's a problem you may need some post-processing.)
这篇关于MySQL中的ROW_NUMBER()的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!