删除其中部分原始重复的记录 [英] Delete record where duplicate in part of raw

问题描述

我需要删除表中的所有记录，该表之间的时间为1或2分钟或相同，并且必须相同。 ID 但保留第一条记录

I need to delete all records in a table where the time between it 1 or 2 minute or the same and must be the same ID but keep the first record

ID             Time             SN  SD   WE FW
10  2014-06-30 19:17:37.000 I   0   100 0
10  2014-06-30 19:17:42.000 I   0   100 0
10  2014-06-30 19:17:46.000 I   0   100 0
10  2014-06-30 19:17:58.000 I   0   100 0
10  2014-06-30 20:37:46.000 I   0   100 0

or

ID             Time             SN  SD   WE FW
10  2014-07-01 21:10:33.000 I   0   100 0
10  2014-07-01 21:11:06.000 O   0   100 0
10  2014-07-02 20:53:36.000 I   0   100 0
10  2014-07-02 20:53:38.000 I   0   100 0
10  2014-07-02 20:54:33.000 O   0   100 0
10  2014-07-02 20:54:41.000 O   0   100 0
10  2014-07-02 20:55:22.000 o   0   100 0

成为

ID             Time             SN  SD   WE FW
10  2014-06-30 19:17:37.000 I   0   100 0
10  2014-06-30 20:37:46.000 I   0   100 0

和

ID             Time             SN  SD   WE FW
10  2014-07-01 21:10:33.000 I   0   100 0
10  2014-07-02 20:53:36.000 I   0   100 0

---

无法正常工作
对不起，我是新用户@sql此MY Table内容

---

ITS NOT Working sorry i am new @sql This MY Table content

SELECT TOP 1000 
      [USERID]
      ,[CHECKTIME]
      ,[CHECKTYPE]
      ,[VERIFYCODE]
      ,[SENSORID]
      ,[WorkCode]
      ,[sn]
FROM 
   [NEWFP].[dbo].[CHECKINOUT]

，我需要删除2或1分钟的记录并保留

and I need to delete record that the time 2 or 1 minute and keep the first but the same user id.

我做不到吗？

推荐答案

去这里！

DECLARE @Seconds INT = 120
DECLARE @Logs TABLE (
    ID INT,
    [Time] DATETIME
)
INSERT @Logs VALUES
(10, '2014-06-30 19:17:37.000'),
(10, '2014-06-30 19:17:42.000'),
(10, '2014-06-30 19:17:46.000'),
(10, '2014-06-30 19:17:58.000'),
(10, '2014-06-30 20:37:46.000'),
(10, '2014-07-01 21:10:33.000'),
(10, '2014-07-01 21:11:06.000'),
(10, '2014-07-02 20:53:36.000'),
(10, '2014-07-02 20:53:38.000'),
(10, '2014-07-02 20:54:33.000'),
(10, '2014-07-02 20:54:41.000'),
(10, '2014-07-02 20:55:22.000')

;WITH Logs AS (
    SELECT
        ID,
        [Time],
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [Time]) AS RowNum
    FROM @Logs L
)
    --SELECT *, DATEDIFF(SS, L1.[Time], L2.[Time])
    DELETE L2
    FROM Logs L1
        INNER JOIN Logs L2
            ON L1.ID = L2.ID
                AND L1.RowNum = L2.RowNum - 1
    WHERE DATEDIFF(SS, L1.[Time], L2.[Time]) < @Seconds
SELECT * FROM @Logs

这篇关于删除其中部分原始重复的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！