表如下
key value
1 1
1 2
2 2
2 1
2 3
3 1
3 2
要根据不同的key去重,选择出对应的第一个value作为值,当然前面有个自增序的id列
得到如下结果
key value
1 1
2 2
3 1
key value
1 1
1 2
2 2
2 1
2 3
3 1
3 2
要根据不同的key去重,选择出对应的第一个value作为值,当然前面有个自增序的id列
得到如下结果
key value
1 1
2 2
3 1
where not exists(select 1 from tb where [key]=t.[key] and id<t.id)
create table tb
(
[key] int,
[value] int
)insert into tb values(1,1)
insert into tb values(1,2)
insert into tb values(2,2)
insert into tb values(2,1)
insert into tb values(2,3)
insert into tb values(3,1)
insert into tb values(3,2)
select [key],[value] from
(
select id = row_number() over (partition by [key] order by getdate()) ,* from tb
) t where not exists (select 1 from
(
select id = row_number() over (partition by [key] order by getdate()) ,* from tb
) t1
where [key]=t.[key] and id < t.id)
/*
key,value
1,1
2,2
3,1(3 行受影响)*/