問題描述

我想刪除重復(fù)項(xiàng)，例如我想刪除這一行Test2, 321, 0"，因?yàn)橛兄貜?fù)項(xiàng)，但在第 1 行和第 2 行中，我只想刪除第 2 行，因?yàn)轭愋?id 高于當(dāng)前在第一行重復(fù).

這是我的桌子

我可以使用此查詢刪除第 4 行中的重復(fù)項(xiàng).但我似乎無法弄清楚如何刪除第 2 行，因?yàn)榈?1 行相同但類型編號(hào)較低.如果類型編號(hào)為 2，則獲勝，您必須刪除具有相同名稱和記錄編號(hào)的 0 和 1 類型編號(hào)的任何重復(fù)項(xiàng).

WITH dup2 as (選擇名稱, 記錄號(hào), 鍵入 ROW_NUMBER() OVER(PARTITION BY Name, RecordNum, Type ORDER BY ID ASC) AS NumOfDups從 MyTbale)從 dup2 中刪除 NumOfDups >1

所以基本上你只想為每個(gè) Name, RecordNum 組保留一條記錄.如果Type相同只保留最低的ID，如果Type不同保留最低的類型:

帶有 dup2 AS(選擇姓名，記錄數(shù)，NumOfDups = ROW_NUMBER()OVER(按名稱分區(qū)，RecordNumORDER BY CASE WHEN Type=2 THEN 0 ELSE 1 END, Type, Id)從 MyTbale)刪除從 dup2WHERE NumOfDups >1

SQL-Fiddle 演示

編輯根據(jù)評(píng)論:假設(shè)當(dāng) type = 2 時(shí)，我想刪除類型為 0 或 1 的其他匹配記錄，因此這里有兩個(gè)獲勝."

I want to delete duplicates for example I want to delete this row "Test2, 321, 0" because there is a duplicate, but in row 1 and 2 I only want to delete row two because the type id is higher then the current duplicate in row one.

This is my table

ID, Name, RecordNum, Type
--------------------------
1, Test, 123, 0
2, Test, 123, 1
3, Test2, 321, 0
4, Test2, 321, 0

I can delete duplicate in row 4 using this query. but I cannot seem to figure out how to delete row 2 because row 1 is the same but type number is lower. and if the type number is 2 it wins and you have to delete any duplicates with 0 and 1 type numbers that have the same name and recordnum.

WITH dup2 as ( 
  SELECT Name
       , RecordNum
       , Type ROW_NUMBER() OVER(PARTITION BY Name, RecordNum, Type ORDER BY ID ASC) AS NumOfDups 
    FROM MyTbale) 
  delete FROM dup2 WHERE NumOfDups > 1

So basically you want to keep only one record for each Name, RecordNum group. If the Type is the same keep only the lowest ID, if the Type is different keep the lowest type:

WITH dup2 AS 
( 
         SELECT  NAME, 
                 RecordNum, 
                 NumOfDups = ROW_NUMBER()OVER(
                                 PARTITION BY NAME, RecordNum 
                                 ORDER BY CASE WHEN Type=2 THEN 0 ELSE 1 END, Type, Id)
         FROM    MyTbale) 
DELETE 
FROM   dup2 
WHERE  NumOfDups > 1

SQL-Fiddle Demo

Edited according to comment: "Let say when the type = 2 then I want to delete the other matching records that have a zero or one for type, so two wins here."

這篇關(guān)于僅在sql server中的條件下刪除重復(fù)記錄的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！