如何在SQL中删除副本而只保留一个副本?

时间:2021-12-03 19:22:53

I have the following table in SQL with lines of an order as follows:

我在SQL中有以下的表格,顺序如下:

RowId     OrderId     Type     Text
----------------------------------------
 1        1           5        "Sometext"
 2        1           5        "Sometext"
 3        2           4        "Sometext"
 4        3           5        "Sometext"
 5        2           4        "Sometext"
 6        1           3        "Sometext"

Each order cannot have a duplicate type, but can have multiple different types.

每个订单不能有一个重复的类型,但是可以有多个不同的类型。

Rows 1 and 2 are duplicates for Order 1, but row 6 is fine.

第1行和第2行是订单1的副本,但是第6行没问题。

Rows 3 and 5 are duplicates for Order 2.

第3行和第5行是订单2的副本。

I need to delete all of the duplicated data, so in this case I need to delete row 2 and row 5.

我需要删除所有重复的数据,因此在本例中,我需要删除第2行和第5行。

What is the best query to delete the data? Or even just return a list of RowID's that contain duplicates to be deleted (or the opposite, a list of RowID's to be kept)?

删除数据的最佳查询是什么?或者仅仅返回一个包含重复的RowID的列表(或者相反的,保留一个RowID的列表)?

Thanks.

谢谢。

3 个解决方案

#1


3  

Try a simple approach:

尝试一个简单的方法:

DELETE FROM t
WHERE rowid NOT IN (
  SELECT min(rowid) FROM t
  GROUP BY orderid, type
)

Fiddle here.

小提琴。

Note that it seems you want to keep the lowers rowid when it is repeated. That's why I'm keeping the min.

注意,当重复时,您似乎希望保持低rowid。这就是我保留最小值的原因。

#2


1  

Please try:

请尝试:

with c as
(
  select *, row_number() over(partition by OrderId, Type order by (select 0)) as n
  from YourTable
)
delete from c
where n > 1;

#3


1  

;with cte as
(
Select Row_Number() Over(Partition BY ORDERID,TYPE ORDER BY RowId) as Rows,
RowId   ,  OrderId   ,  Type  ,   Text from TableName
)
Select RowId   ,  OrderId   ,  Type  ,   Text from cte where Rows>1

Sql Fiddle Demo

Sql小提琴演示

#1


3  

Try a simple approach:

尝试一个简单的方法:

DELETE FROM t
WHERE rowid NOT IN (
  SELECT min(rowid) FROM t
  GROUP BY orderid, type
)

Fiddle here.

小提琴。

Note that it seems you want to keep the lowers rowid when it is repeated. That's why I'm keeping the min.

注意,当重复时,您似乎希望保持低rowid。这就是我保留最小值的原因。

#2


1  

Please try:

请尝试:

with c as
(
  select *, row_number() over(partition by OrderId, Type order by (select 0)) as n
  from YourTable
)
delete from c
where n > 1;

#3


1  

;with cte as
(
Select Row_Number() Over(Partition BY ORDERID,TYPE ORDER BY RowId) as Rows,
RowId   ,  OrderId   ,  Type  ,   Text from TableName
)
Select RowId   ,  OrderId   ,  Type  ,   Text from cte where Rows>1

Sql Fiddle Demo

Sql小提琴演示