写了一个简洁的删除重复行的case

用了aggregate函数和temp表。在SQLServer 2008下执行通过。

可以处理存在不同主键的重复数据。

 

create table t
(a int,
b char(10),
c char(10)
)

insert into t values (1, 'aa','bb')
insert into t values (2, 'aa','bb')
insert into t values (3, 'aa','bb')
insert into t values (4, 'cc','bb')
insert into t values (5, 'cc','bb')
insert into t values (6, 'aa','cc')

with tmp as(
select a,row_number () over ( partition by b,c order by b,c) as rownum from t
)
delete from tmp where rownum<>1

select * from t

 

转载于:https://www.cnblogs.com/ImToffee/p/3683486.html