問題描述
在 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 以獲得具有最高 col3
對于每個 (col1, col2)
對.
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).
我經常喜歡空自連接:
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()的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!