删除条件重复的行（使用SQL和LINQ） [英] Deleting duplicate rows with condition(Using SQL and LINQ)

问题描述

我有一个有5列的表格

ID

CPT4

CPT4Mod

ItemCharge

Eff_Date

删除条件：



当行存在重复的 CPT4 和 CPT4Mod 字段值但不同的 ItemCharge 和 Eff_Date 值，排除除最近的 Eff_Date 记录之外的所有记录。



我需要一个可以在两种情况下实现此目的的查询。 />


1.当用户提供CPT4Mod时。

2.当用户未提供CPT4Mod时



后来我需要使用LINQ实现相同的功能。

解决方案

  SELECT  * 
  FROM  
（
  SELECT  ROW_NUMBER（） OVER （ PARTITION   BY  CPT4，CPT4MOD，ITEMCHARGE  ORDER   BY  EFF_DATE  DESC ） AS  RowNo 
，* 
  FROM  #table 
） AS  T 
  WHERE  T.RowNo =  1  

  WITH  CTE  AS （
  SELECT  COL1，COL2，COL3，COL4，
 RN = ROW_NUMBER（） OVER （ PARTITION   BY  COL1  ORDER   BY  COL1）
  FROM  表名称
）
  DELETE   FROM  CTE  WHERE  RN>  1  

即时，当然没有经过测试，很可能是性能问题：

  DELETE   FROM  MyTable MyTable_Delete 
  WHERE   EXISTS （ SELECT  MyTable_Cond.ID  FROM  MyTable MyTable_Cond 
  WHERE  MyTable_Cond.CPT4 = MyTable_Delete.CPT4 
  AND  MyTable_Cond.CPT4Mod = MyTable_Delete.CPT4Mod 
  AND  MyTable_Cond.ItemCharge< > MyTable_Delete.ItemCharge 
  AND  MyTable_Cond.Eff_Date<> MyTable_Delete.Eff_Date）
  AND  MyTable _Delete.Eff_Date< （ SELECT  MAX（MyTable_Latest.Eff_Date）
  FROM  MyTable MyTable_Latest 
  WHERE  MyTable_Latest.CPT4 = MyTable_Delete.CPT4 
  AND  MyTable_Latest.CPT4Mod = MyTable_Delete.CPT4Mod 
 < span class =code-keyword> AND  MyTable_Latest.ItemCharge<> MyTable_Delete.ItemCharge 
  AND  MyTable_Latest.Eff_Date<> MyTable_Delete.Eff_Date）

I have a table with 5 columns

ID

CPT4

CPT4Mod

ItemCharge

Eff_Date

Deletion Condition:

When rows exists with duplicate CPT4 and CPT4Mod field values but different ItemCharge and Eff_Date values, exclude all but the most recent Eff_Date records.

I need a query which can achieve this in two scenarios.

1. When CPT4Mod is given by the user.
2. When CPT4Mod is not given by the user.

Later I need to achieve the same using LINQ.

解决方案

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY CPT4, CPT4MOD, ITEMCHARGE ORDER BY EFF_DATE DESC) AS RowNo
    , *
FROM #table
) AS T
WHERE T.RowNo = 1

WITH CTE AS(
       SELECT COL1, COL2, COL3,COL4,
RN = ROW_NUMBER()OVER(PARTITION BY COL1 ORDER BY COL1)
   FROM TABLE NAME
)
DELETE FROM CTE WHERE RN > 1

Just on the fly and of course not tested and most probably performance problems:

DELETE FROM MyTable MyTable_Delete
WHERE EXISTS (SELECT MyTable_Cond.ID FROM MyTable MyTable_Cond
               WHERE MyTable_Cond.CPT4 = MyTable_Delete.CPT4
                 AND MyTable_Cond.CPT4Mod = MyTable_Delete.CPT4Mod
                 AND MyTable_Cond.ItemCharge <> MyTable_Delete.ItemCharge
                 AND MyTable_Cond.Eff_Date <> MyTable_Delete.Eff_Date)
            AND MyTable_Delete.Eff_Date < (SELECT MAX(MyTable_Latest.Eff_Date)
                                             FROM MyTable MyTable_Latest
                                             WHERE MyTable_Latest.CPT4 = MyTable_Delete.CPT4
                                             AND MyTable_Latest.CPT4Mod = MyTable_Delete.CPT4Mod
                                             AND MyTable_Latest.ItemCharge <> MyTable_Delete.ItemCharge
                                             AND MyTable_Latest.Eff_Date <> MyTable_Delete.Eff_Date)

这篇关于删除条件重复的行（使用SQL和LINQ）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！