获取每个 ID 的最新记录 [英] get latest record for each ID
本文介绍了获取每个 ID 的最新记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想获取每台服务器的最新记录.
I would like to get the latest record for each server.
以下是一些示例数据:
TimeGenerated SourceName ComputerName Message
2014-11-22 21:48:30 Windows Update Agent Server1 Update Failed
2014-11-22 21:42:30 Windows Update Agent Server2 Update Failed
2014-11-22 21:45:30 Windows Update Agent Server2 Update Failed
2014-11-22 21:43:30 Windows Update Agent Server1 Update Failed
期望的输出:
TimeGenerated SourceName ComputerName Message
2014-11-22 21:48:30 Windows Update Agent Server1 Update Failed
2014-11-22 21:45:30 Windows Update Agent Server2 Update Failed
我试过了:
SELECT * FROM TABLE
GROUP BY ComputerName
ORDER BY TimeGenerated ASC
但这会输出不一致的结果,并且在大多数情况下不会给我最新的结果.
But that outputs inconsistent results and does not give me the latest in most cases.
我也尝试了一些子查询,但都失败了.
I also tried some sub queries, but failed miserably.
推荐答案
SELECT *
FROM yourtable
INNER JOIN (
SELECT MAX(timeGenerated) as maxtime, ComputerName
FROM yourtable
GROUP BY ComputerName
) AS latest_record ON (yourtable.timeGenerated = maxtime)
AND (latest_record.ComputerName = yourtable.ComputerName)
内部查询获取每个计算机名称的最新时间戳.然后,外部查询根据内部查询找到的时间/计算机名称,结合该查询结果从表中获取其余字段.如果您以相同的最大次数记录了两个事件,您将获得该计算机名的两条记录.
Inner query gets the latest timestamp for every computer name. The outer query then joins against that query result to fetch the rest of the fields from the table, based on the time/computername the inner query finds. If you have two events logged with identical max times, you'd get two records for that computername.
这篇关于获取每个 ID 的最新记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文