删除重复的sql行

l5tcr1uw  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(274)

我应该从表中删除重复的行。我的表结构是:clientid giftid invoice quantite
重复行示例:

Example 1:
 1. C1-G1-Inv1-1
 2. C1-G1-Inv1-0
 3. C1-G1-NULL-NULL

预期结果:应删除2和3
例2:

1. C2-G1-NULL-NULL
 2. C2-G1-NULL-NULL
 3. C2-G1-NULL-NULL

预期结果:接受任何行
我的删除查询是:

WITH CTE AS(
   SELECT ClientID,GiftID,Invoice,Quantity,
       RN = ROW_NUMBER()OVER(PARTITION BY ClientID,GiftID,Invoice,Quantity ORDER BY 
 ClientID,GiftID,Invoice)
   FROM #RowsToDelete
)
Delete FROM CTE where RN > 1

我对row\u number()函数的用法还不太熟悉,我想我还没有弄清楚如何正确使用它

fhity93d

fhity93d1#

您需要像下面这样更改partitionby和order by子句-

WITH CTE AS(
   SELECT ClientID,GiftID,Invoice,Quantity,
       RN = ROW_NUMBER() OVER(PARTITION BY ClientID,GiftID ORDER BY Quantite desc)
   FROM #RowsToDelete
)
Delete FROM CTE where RN > 1

相关问题