我正在编写查询以查找重复记录。我有以下列的表格
Id, Deliveries, TankId, Timestamp.
我已经插入了重复记录,即同一坦克, 1天的偏移时间戳。
。现在,我想删除带有较小时间戳的重复记录。
例如。我在7月24日和25日为同一坦克增加了重复的交付。我需要删除第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 od.*
FROM (SELECT od.*,
ROW_NUMBER() OVER (PARTITION BY od.TankId, od.Delivery ORDER BY od.TimeStamp DESC) as seqnum
FROM [dbo].[tObservationData] od
) od
WHERE seqnum = 1;
在这种情况下,您可以按子句按顺序使用分区。您可以按TANKID,交货和订单按时间戳记按DESC顺序进行分区
Select * from (
Select *,ROW_NUMBER() OVER (PARTITION BY TankID,Delievry ORDER BY [Timestamp] DESC) AS rn
from [dbo].[ObservationData]
)
where rn = 1
在上述代码记录中,rn = 1将具有最新的时间戳。因此,您只能选择这些并忽略其他人。另外,您可以使用相同的内容来删除/删除表中的记录。
WITH TempObservationdata (TankID,Delivery,Timestamp)
AS
(
SELECT TankID,Delivery,ROW_NUMBER() OVER(PARTITION by TankID, Delivery ORDER BY Timsetamp desc)
AS Timestamp
FROM dbo.ObservationData
)
--Now Delete Duplicate Rows
DELETE FROM TempObservationdata
WHERE Timestamp > 1
认为它将起作用
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