oracle 数据库 去重（删除只留一条数据）

思路

第一步：查询出重复数据，并标明行号

select t.*,row_number() over(partition by t.ordercode,t.mobilephone,t.platenumber,t.ordercreatedate,t.amount,t.ordernum order by t.pid)su from three_order t

第二步：筛选出行号大于1

select tt.*from (
select t.*,row_number() over(partition by t.ordercode,t.mobilephone,t.platenumber,t.ordercreatedate,t.amount,t.ordernum order by t.pid)su from three_order t
) tt

where tt.su >1

第三步：删除重复数据，只留一条

delete three_order
where pid in (

select tt.pid from (
select t.*,row_number() over(partition by t.ordercode,t.mobilephone,t.platenumber,t.ordercreatedate,t.amount,t.ordernum order by t.pid)su from three_order t
) tt

where tt.su >1)

以后面试 或者笔试时 遇到“请用 一条sql语句删除重复数据”这个问题，咔咔几巴掌给面试官。哈哈！！！