22,209
社区成员
发帖
与我相关
我的任务
分享
declare @tb table(ID int,[Type] int,[Data] int)
insert into @tb(ID,[Type],[Data])
select * from (
select 1,1,16436 union all
select 2,2,2345 union all
select 3,3,2154 union all
select 4,1,143 union all
select 5,1,5 union all
select 6,2,74 union all
select 7,2,542 union all
select 8,1,623 union all
select 9,3,5123
) t(ID,[Type],[Data])
select * from @tb
delete t1 from @tb as t1
inner join
(
select *,row_number()over(partition by [type] order by [data] desc) as SeqNo from @tb
) t2 on t2.id=t1.id and t2.SeqNo!=1
select * from @tb
删除前:
/*
ID Type Data
1 1 16436
2 2 2345
3 3 2154
4 1 143
5 1 5
6 2 74
7 2 542
8 1 623
9 3 5123
*/
删除后:
/*
ID Type Data
1 1 16436
2 2 2345
9 3 5123
*/
select a.*
from 表A a
where not exists(select 1 from 表A b where b.Type=a.Type and b.Data<a.Data)
with t(id,Type,data) as (
select 1,1,16436 union all
select 2,2,2345 union all
select 3,3,2154 union all
select 4,1,143 union all
select 5,1,5 union all
select 6,2,74 union all
select 7,2,542 union all
select 8,1,623 union all
select 9,3,5123
)
select id,Type,data
from (
select *
,ROW_NUMBER()over(partition by type order by data)rn
from t
) tt
where tt.rn=1