問題描述

我有以下列的記錄: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 個(gè)數(shù)據(jù)庫表，每個(gè)表中有數(shù)千條記錄(當(dāng)然還有更多的列，我只提到了那些對解決方案有影響的列).記錄從PLC發(fā)送到數(shù)據(jù)庫，但有時(shí)(我們不知道為什么)PLC也會發(fā)送錯誤的記錄.

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.

所以我需要一個(gè)查詢來查找那些錯誤的記錄并刪除它們.:)

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

有人知道 SQL 代碼應(yīng)該是什么樣的嗎?

Anybody who knows how the SQL code should look like?

推薦答案

請看下面我的 SQL.首先，我們使用兩個(gè)窗口函數(shù) (LEAD) 收集要刪除的 id，以獲取下一行所需的數(shù)據(jù).然后，計(jì)算所有需要的數(shù)據(jù)后，應(yīng)用 OP 提出的評估規(guī)則.最后，使用獲取到的 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)
                           )

這篇關(guān)于根據(jù)下一條記錄刪除SQL中的記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！