delete stab from stab t1 inner join ( select *,row_number() over(partition by cl1,cl2 order by cl1) as row from stab ) t2 on t1.cl1 = t2.cl1 and t1.cl2 = t2.cl2 where t2.row >1
(1,1,'重复数据','f') --与第1条数据重复数据 select * from stab --会删除cl1和cl2这两列的数据相同的行,cl3和cl4列可以不同 --delete stab with t1 as ( select *,row_number() over(partition by cl1,cl2 order by cl1) as row from stab )delete t1 from t1 inner join t1 as t2 on t1.cl1 = t2.cl1 and t1.cl2 = t2.cl2 and t1.row = t2.rowwhere t2.row >=2
delete stab
from stab t1
inner join
(
select *,row_number() over(partition by cl1,cl2 order by cl1) as row
from stab
) t2
on t1.cl1 = t2.cl1
and t1.cl2 = t2.cl2 where t2.row >1
哦,由于上面的帖子里的rowid是oracle专用的,所以我的代码中通过row_number()函数来动态算出来,新增为row这一列,那么这样就可以了。
values(1,1,'a','b'),
(1,2,'e','f'),
(2,1,'c','d'),
(3,1,'e','f'),
(3,2,'a','b'),
(2,1,'重复数据','d'), --与第2条数据重复重复数据
(1,1,'重复数据','f') --与第1条数据重复数据
select *
from stab
--会删除cl1和cl2这两列的数据相同的行,cl3和cl4列可以不同
--delete stab with t1
as
(
select *,row_number() over(partition by cl1,cl2 order by cl1) as row
from stab
)delete t1
from t1
inner join t1 as t2
on t1.cl1 = t2.cl1
and t1.cl2 = t2.cl2
and t1.row = t2.rowwhere t2.row >=2