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

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

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

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

;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