仅在sql server中的条件下删除重复记录

问题描述

我想删除重复项，例如我想删除这一行Test2, 321, 0"，因为有重复项，但在第 1 行和第 2 行中，我只想删除第 2 行，因为类型 id 高于当前在第一行重复.

这是我的桌子

我可以使用此查询删除第 4 行中的重复项.但我似乎无法弄清楚如何删除第 2 行，因为第 1 行相同但类型编号较低.如果类型编号为 2，则获胜，您必须删除具有相同名称和记录编号的 0 和 1 类型编号的任何重复项.

WITH dup2 as (选择名称, 记录号, 键入 ROW_NUMBER() OVER(PARTITION BY Name, RecordNum, Type ORDER BY ID ASC) AS NumOfDups从 MyTbale)从 dup2 中删除 NumOfDups >1

所以基本上你只想为每个 Name, RecordNum 组保留一条记录.如果Type相同只保留最低的ID，如果Type不同保留最低的类型:

带有 dup2 AS(选择姓名，记录数，NumOfDups = ROW_NUMBER()OVER(按名称分区，RecordNumORDER BY CASE WHEN Type=2 THEN 0 ELSE 1 END, Type, Id)从 MyTbale)删除从 dup2WHERE NumOfDups >1

SQL-Fiddle 演示

编辑根据评论:假设当 type = 2 时，我想删除类型为 0 或 1 的其他匹配记录，因此这里有两个获胜."

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."