根据下一条记录删除SQL中的记录

2021-09-10 00:00:00 sql tsql sql-server

我有以下列的记录:ID、Time_End 和 Attribute.

I have records with columns: ID, Time_End and Attribute.

我需要删除所有记录，

WHERE Time_End = '1990-01-01 00:00:00.000' AND Attribute <> '9'

但仅限:

  • 如果下一行没有相同的属性编号

或

  • 下一行具有相同的属性编号和 Time_End 值 1990-01-01 00:00:00.000

例如:

ID     Time_End                     Attribute
---------------------------------------------
235    1990-01-01 00:00:00.000      5                  /delete
236    1990-01-01 00:00:00.000      5                  /delete
237    1990-01-01 00:00:00.000      5
238    2016-10-10 23:45:40.000      5


ID     Time_End                     Attribute
---------------------------------------------
312    1990-01-01 00:00:00.000      8                  /delete
313    2016-01-09 18:00:00.000      6                  
314    1990-01-01 00:00:00.000      4                  /delete
315    1990-01-01 00:00:00.000      7
316    2016-10-10 23:45:40.000      7

我们的客户有 50 个数据库表，每个表中有数千条记录(当然还有更多的列，我只提到了那些对解决方案有影响的列).记录从PLC发送到数据库，但有时(我们不知道为什么)PLC也会发送错误的记录.

Our customer have 50 database tables with thousands of records in every table (and of course more columns, I mentioned only those, which have impact on solution). Records are send in to the database from PLC, but sometimes (we don't know why) PLC send also wrong records.

所以我需要一个查询来查找那些错误的记录并删除它们.:)

So what I need is a query which finds those wrong records and deletes them. :)

有人知道 SQL 代码应该是什么样的吗?

Anybody who knows how the SQL code should look like?

推荐答案

请看下面我的 SQL.首先，我们使用两个窗口函数 (LEAD) 收集要删除的 id，以获取下一行所需的数据.然后，计算所有需要的数据后，应用 OP 提出的评估规则.最后，使用获取到的 id 通过带有 in 子句的 id 删除 tablet 受影响的记录.

Please see my SQL below. First, we collect ids to delete using two window functions (LEAD) to get the next row needed data. Then, with all needed data computed, apply the evaluation rules proposed by the OP. Last, use the obtained ids to delete the affected records of the tablet by id with an in clause. 

 DELETE toDeleteTable 
 WHERE toDeleteTable.id IN (WITH dataSet
                              AS (SELECT toDeleteTable.id,
                                         toDeleteTable.time_end, 
                                         toDeleteTable.attribute, 
                                         LEAD(toDeleteTable.time_end,1,0)  OVER (ORDER BY toDeleteTable.id) AS next_time_end,
                                         LEAD(toDeleteTable.attribute,1,0) OVER (ORDER BY toDeleteTable.id) AS next_attribute
                                    FROM toDeleteTable)
                            SELECT dataSet.id
                              FROM dataSet
                             WHERE dataSet.time_end = '1990-01-01 00:00:00.000' 
                               AND dataSet.attribute <> '9' 
                               AND (  (dataSet.next_attribute = dataSet.attribute AND dataSet.next_time_end  = '1990-01-01 00:00:00.000')   
                                    OR dataSet.next_attribute <> dataSet.attribute)
                           )

相关文章