思路
第一步:查询出重复数据,并标明行号
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语句删除重复数据”这个问题,咔咔几巴掌给面试官。哈哈!!!