问题描述
我有以下列的记录: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)
)
这篇关于根据下一条记录删除SQL中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！