日期:2014-05-17 浏览次数:20546 次
create table stab(cl1 int,cl2 int,cl3 varchar(10),cl4 varchar(10))
--truncate table stab
insert stab (cl1,cl2,cl3,cl4)
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.row
where t2.row >=2