删除指定数量的t-sql代码

[ds345]

我有两个表

第一个表存放某个 leibie的总数量及需要删除数量
leibie zongshu del_shu
a 5 3
b 6 3

第二个表存放类别及顺序，删除的时候按照order从大到小的顺序进行删除

leibie order
a 1
a 2
a 3
a 4
a 5
b 1
b 2
b 3
b 4
b 5
b 6


第二个表删除后 的结果为
leibie order
a 1
a 2
b 1
b 2
b 3


求t-sql代码

[道素]

有两种办法，一种执行多次删除，一种是用一句

if object_id('tempdb..#t1') is not null drop table #t1
create table #t1(leibie varchar(100),zongshu int,del_shu int)
insert into #t1
select 'a',5,3 union all
select 'b',6,3
if object_id('tempdb..#t2') is not null drop table #t2
create table #t2(leibie varchar(100),[order] int)
insert into #t2
select 'a',1  union all
select 'a',2  union all
select 'a',3  union all
select 'a',4  union all
select 'a',5  union all
select 'b',1  union all
select 'b',2  union all
select 'b',3  union all
select 'b',4  union all
select 'b',5  union all
select 'b',6

declare @s nvarchar(max)
/*
--多次删除
select @s=isnull(@s+';','')+'delete t from (select *,row_number()over(partition by leibie order by [order] desc) as seq from #t2) as t where t.leibie='''+leibie+''' and [seq]<= '+ltrim(del_shu)+char(13) from #t1
--print @s
--exec(@s)
*/
--一次删除
set  @s ='delete t from (select *,row_number()over(partition by leibie order by [order] desc) as seq from #t2) as t where  [seq]<=case '
select @s=@s+' when leibie='''+leibie + ''' then ' +ltrim(del_shu) from #t1
set @s= @s+' end '
exec(@s)

select * from #t2