sql - SQL - 删除所有重复项并仅保留一个

Question

我有一张如下表

Order_Id (PK), Customer_ID, Order_Amount
  A01             AA01         100
  A01             AA01         100 (duplicate)
  A02             AA01         200
  A03             AA02         300

我想删除重复的并保留剩余的

我可以使用下面选择重复项

select *
from 
(
select order_id, customer_id, order_amount, row_number () over (partition by order_id order by order_Id) as rnk
from mytable
) where rnk > 1

但是从 mytable 中删除这条记录的下一步是什么？我需要创建一个临时表吗？

谢谢！

标签： sqlamazon-redshift

Answer 1

它应该是作品。

SELECT *
FROM 
(
  SELECT 
     order_id, 
     customer_id, 
     order_amount, 
     row_number () OVER (partition by order_id order by order_Id) as rnk
  FROM mytable
  GROUP BY order_id
) WHERE rnk > 1