/*
删除表中重复行的方法
*/
if exists(select 1 from sys.tables where name='t_dup')
drop table t_dup
go
create table t_dup(id int,age int,name varchar(32))
insert into t_dup
select 1,1,'a'
union all select 1,1,'a'
union all select 1,1,'a'
union all select 1,2,'a'
union all select 1,2,'e'
union all select 2,3,'b'
union all select 3,3,'d'
go
select * from t_dup
go
with t_all as
(select id,age,name,row_number() over(order by id) [num]
from t_dup),
t_max as
(select id,age,name,max(num) num
from t_all
group by id,age,name)
select id,name,age from t_all a where exists(select 1 from t_max where num=a.num)
删除表中重复行的方法
*/
if exists(select 1 from sys.tables where name='t_dup')
drop table t_dup
go
create table t_dup(id int,age int,name varchar(32))
insert into t_dup
select 1,1,'a'
union all select 1,1,'a'
union all select 1,1,'a'
union all select 1,2,'a'
union all select 1,2,'e'
union all select 2,3,'b'
union all select 3,3,'d'
go
select * from t_dup
go
with t_all as
(select id,age,name,row_number() over(order by id) [num]
from t_dup),
t_max as
(select id,age,name,max(num) num
from t_all
group by id,age,name)
select id,name,age from t_all a where exists(select 1 from t_max where num=a.num)