SQL SQL获取上次日期时间记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16550703/
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
SQL get the last date time record
提问by Miguel
I'm trying to get the last datetime record from a table that happens to store multiple status. My table looks like so:
我正在尝试从恰好存储多个状态的表中获取最后一个日期时间记录。我的桌子看起来像这样：
+---------+------------------------+-------+
|filename |Dates |Status |
+---------+------------------------+-------+
|abc.txt |2012-02-14 12:04:45.397 |Open |
|abc.txt |2012-02-14 12:14:20.997 |Closed |
|abc.txt |2013-02-14 12:20:59.407 |Open |
|dfg.txt |2012-02-14 12:14:20.997 |Closed |
|dfg.txt |2013-02-14 12:20:59.407 |Open |
+---------+------------------------+-------+
The results should be
结果应该是
+---------+------------------------+-------+
|filename |Dates |Status |
+---------+------------------------+-------+
|abc.txt |2013-02-14 12:20:59.407 |Open |
|dfg.txt |2013-02-14 12:20:59.407 |Open |
+---------+------------------------+-------+
采纳答案by Nicholas Carey
If you want one row for each filename, reflecting a specific states and listing the most recent date then this is your friend:
如果您希望每个文件名占一行，反映特定状态并列出最近的日期，那么这就是您的朋友：
select filename ,
status ,
max_date = max( dates )
from some_table t
group by filename , status
having status = '<your-desired-status-here>'
Easy!
简单！
回答by vidit
SELECT * FROM table
WHERE Dates IN (SELECT max(Dates) FROM table);
回答by Renan
SELECT TOP 1 * FROM foo ORDER BY Dates DESC
Will return one result with the latest date.
将返回一个具有最新日期的结果。
SELECT * FROM foo WHERE foo.Dates = (SELECT MAX(Dates) FROM foo)
Will return all results that have the same maximum date, to the milissecond.
将所有具有相同最大日期的结果返回到毫秒。
This is for SQL Server. I'll leave it up to you to use the DATEPARTfunction if you want to use dates but not times.
这是针对 SQL Server 的。如果您想使用日期而不是时间，我会让您使用DATEPART函数。
回答by shikos
this working
这个工作
SELECT distinct filename
,last_value(dates)over (PARTITION BY filename ORDER BY filename)posd
,last_value(status)over (PARTITION BY filename ORDER BY filename )poss
FROM distemp.dbo.Shmy_table
回答by Osy
Considering that max(dates) can be different for each filename, my solution :
考虑到每个文件名的 max(dates) 可能不同，我的解决方案是：
select filename, dates, status
from yt a
where a.dates = (
select max(dates)
from yt b
where a.filename = b.filename
)
;
http://sqlfiddle.com/#!18/fdf8d/1/0
http://sqlfiddle.com/#!18/fdf8d/1/0
HTH
HTH
回答by Duffmaster33
Exact syntax will of course depend upon database, but something like:
确切的语法当然取决于数据库，但类似于：
SELECT * FROM my_table WHERE (filename, Dates) IN (SELECT filename, Max(Dates) FROM my_table GROUP BY filename)
This will give you results exactly what you are asking for and displaying above. Fiddle: http://www.sqlfiddle.com/#!2/3af8a/1/0
这将为您提供您所要求的结果并在上面显示。小提琴：http://www.sqlfiddle.com/#!2 /3af8a/1 /0
回答by wootscootinboogie
select max(dates)
from yourTable
group by dates
having count(status) > 1
回答by wara
Try this:
尝试这个：
SELECT filename,Dates,Status
FROM TableName
WHERE Dates In (SELECT MAX(Dates) FROM TableName GROUP BY filename)
