查詢以使用 MS SQL 中多個伺服器的內部聯接收集最近唯一的 TimeSampled 行

親愛的，我在下面編寫了內部連接查詢以從我的數據庫表中獲取數據。但是這個數據的輸出幾乎是 3k 行。輸出如下。我只需要最近唯一的“TimeSampled”行中的數據

select DISTINCT DisplayName, ObjectName, CounterName, InstanceName, SampleValue, max(TimeSampled) as TimeSampled
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where ObjectName = 'Processor' AND TimeSampled >= DATEADD(MINUTE,-5,GETDATE())
AND DisplayName Like '%'
group by  [PATH], [DisplayName], [ObjectName], [CounterName], [InstanceName], [SampleValue]

如何僅使用關於 DisplayName 的最後一次採樣行檢索結果。我有近 5k 台這樣的伺服器（DisplayName）來檢索數據。

請求需要幫助來實現這一點。

使用ROW_NUMBER() OVER PARTITION可以允許您選擇每個不同值的第一行，在這種情況下DisplayName，僅選擇ROW_NUMBER() = 1外部查詢中的位置。

我已經在下面使用您的查詢將其包含在內，您可能需要進一步調整以適應您的需求。

SELECT DisplayName, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
FROM
(
   select DISTINCT DisplayName, ObjectName, CounterName, InstanceName, SampleValue, max(TimeSampled) as TimeSampled
   ROW_NUMBER() OVER (PARTITION BY DisplayName ORDER BY TimeSampled DESC) DispCount
   from PerformanceDataAllView pdv with (NOLOCK)
   inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
   inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
   where ObjectName = 'Processor' AND TimeSampled >= DATEADD(MINUTE,-5,GETDATE())
   AND DisplayName Like '%'
   group by  [PATH], [DisplayName], [ObjectName], [CounterName], [InstanceName], [SampleValue]
) s
WHERE s.DispCount = 1

引用自：https://dba.stackexchange.com/questions/242143