我正在写一个查询以查找重复的记录。我有以下各列的表
Id, Deliveries, TankId, Timestamp.
我插入了重复的记录,即对于相同的tankid,相同的交货以及+1天的偏移时间戳记。
现在,我想删除时间戳较短的重复记录。
例如,我在7月24日和25日为相同的Tankid添加了重复的交货。我需要删除第24条记录。
我尝试了以下查询;
SELECT raw.TimeStamp,raw.[Delivery],raw.[TankId]
FROM [dbo].[tObservationData] raw
INNER JOIN (
SELECT [Delivery],[TankSystemId]
FROM [dbo].[ObservationData]
GROUP BY [Delivery],[TankSystemId]
HAVING COUNT([ObservationDataId]) > 1
) dup
ON raw.[Delivery] = dup.[Delivery] AND raw.[TankId] = dup.[TankId]
AND raw.TimeStamp >'2019-06-30 00:00:00.0000000' AND raw.[DeliveryL]>0
ORDER BY [TankSystemId],TimeStamp
但是上面也给出了其他记录,我如何查找和删除那些重复的记录?
认为它将起作用
SELECT raw.TimeStamp,raw.[Delivery],raw.[TankId]
FROM [dbo].[tObservationData] raw
INNER JOIN (
SELECT [Delivery],[TankSystemId],min([TimeStamp]) as min_ts
FROM [dbo].[ObservationData]
GROUP BY [Delivery],[TankSystemId]
HAVING COUNT([ObservationDataId]) > 1
) dup
ON raw.[Delivery] = dup.[Delivery] AND raw.[TankId] = dup.[TankId] and raw.[TimeStamp] = dup.min_ts
AND raw.TimeStamp >'2019-06-30 00:00:00.0000000' AND raw.[DeliveryL]>0
ORDER BY [TankSystemId],TimeStamp
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句