SQL 在多个变量上使用 RANK 的快速帮助
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4716139/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Quick help using RANK over multiple variables
提问by Ray
I need a little help writing a SELECT statement for the following in SQL Server 2008: (example table)
我需要一些帮助，在 SQL Server 2008 中为以下内容编写 SELECT 语句：（示例表）
Date ProductID Year Price
01-01-10 01 2009 1.00
02-01-10 01 2009 2.00
03-01-10 01 2010 3.00
04-01-10 01 2010 4.00
05-01-10 01 2011 5.00
06-01-10 01 2011 6.00
01-01-10 02 2009 1.00
02-01-10 02 2009 2.00
03-01-10 02 2010 3.00
04-01-10 02 2010 4.00
05-01-10 02 2011 5.00
06-01-10 02 2011 6.00
01-01-10 03 2009 1.00
02-01-10 03 2009 2.00
03-01-10 03 2010 3.00
04-01-10 03 2010 4.00
05-01-10 03 2011 5.00
06-01-10 03 2011 6.00
01-01-10 04 2009 1.00
02-01-10 04 2009 2.00
03-01-10 04 2010 3.00
04-01-10 04 2010 4.00
05-01-10 04 2011 5.00
06-01-10 04 2011 6.00
For each unique, ProductID-Year combination (e.g. 01-2009, 03-2011), I need to grab the line with the latest date. The actual data isn't so well-organized--there might only be 1 record for 01-2009, and 15 records for 03-2009.
对于每个唯一的 ProductID-Year 组合（例如 01-2009、03-2011），我需要获取最新日期的行。实际数据并没有组织得很好——01-2009 可能只有 1 条记录，03-2009 可能只有 15 条记录。
I think I have to use DENSE RANKbut I'm not sure.
我想我必须使用，DENSE RANK但我不确定。
回答by Joe Stefanelli
row_numbershould be sufficient for your needs.
row_number应该足以满足您的需求。
Note: I'm assuming your Date column is a true Date or DateTime datatype and not a string in the form you've shown. If that assumption is wrong, some additional string manipulation would be needed to convert Date into a sortable format.
注意：我假设您的 Date 列是真正的 Date 或 DateTime 数据类型，而不是您显示的表单中的字符串。如果该假设是错误的，则需要进行一些额外的字符串操作才能将 Date 转换为可排序的格式。
;with cteRowNumber as (
select Date, ProductID, Year, Price,
row_number() over (partition by ProductID, Year order by Date desc) as RowNum
from YourTable
)
select Date, ProductID, Year, Price
from cteRowNumber
where RowNum = 1
