从 SQL Server 表中选择具有最大日期的不同行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/806849/
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
Select distinct row with max date from SQL Server table?
提问by schooner
I need to get a set of distinct records for a table along with the max date across all the duplciates.
我需要为一个表获取一组不同的记录以及所有重复项的最大日期。
ex:
前任：
Select distinct a,b,c, Max(OrderDate) as maxDate
From ABC
Group By a,b,c
The issue is I get a record back for each different date.
问题是我会为每个不同的日期取回记录。
Ex:
前任：
aaa, bbb, ccc, Jan 1 2009
aaa, bbb, ccc, Jan 28 2009
How can I limit this so I end up with only:
我怎样才能限制这个，所以我最终只得到：
aaa, bbb, ccc Jan 28 2009
I assume the issue is the gorup by and distinct not getting along well.
我认为问题是团伙相处得不好。
EDIT: Found the issue that was causing the problem, query results were as expected, not as above.
编辑：找到导致问题的问题，查询结果符合预期，而不是如上。
回答by Adam Robinson
Something is wrong either with your query or with your example results, as what you describe shouldn't be possible. How about some actual SQL and actual results?
您的查询或示例结果有问题，因为您所描述的应该是不可能的。一些实际的 SQL 和实际结果如何？
In any event, you don't need distinctthere since all you're selecting are your three grouped columns an an aggregate, so you'll by definition end up with all distinct rows. I've never tried this, so perhaps there is some misbehavior when using both of those. Have you tried removing the distinct? What caused you to put it there?
在任何情况下，您都不需要distinct那里，因为您选择的只是您的三个分组列和一个聚合，因此根据定义，您最终会得到所有不同的行。我从来没有尝试过这个，所以在使用这两种方法时可能会有一些不当行为。你有没有试过删除distinct？是什么让你把它放在那里？
回答by Quassnoi
WITH q AS (
SELECT abc.*, ROW_NUMBER() OVER (PARTITION BY a, b, c ORDER BY orderDate DESC) AS rn
FROM abc
)
SELECT *
FROM q
WHERE rn = 1
Having an index on (a, b, c, orderDate)(in this order) will greatly improve this query.
拥有(a, b, c, orderDate)（按此顺序）索引将大大改善此查询。
回答by JoshBerke
If you run this query:
如果您运行此查询：
select 'ab' as Col1, 'bc' as col2, 'cd' as col3, getdate() as Date
into #temp
insert into #temp
values ('ab','bc','cd','1/15/09')
insert into #temp
values ('aa','bb','cc','1/1/09')
insert into #temp
values ('aa','bb','cc','1/22/09')
select col1,col2,col3,max(date)
from #temp
group by col1,col2,col3
You should get back:
你应该回来：
aa, bb, cc, 2009-01-22 00:00:00.000
ab, bc, cd, 2009-04-30 09:23:07.090
aa, bb, cc, 2009-01-22 00:00:00.000
ab, bc, cd, 2009-04-30 09:23:07.090
Your query will work as well so something is really wrong or you have not properly communicated the exact nature of your code.
您的查询也能正常工作，因此确实有问题，或者您没有正确传达代码的确切性质。
