--在邹建的基础上改进一下!
--测试数据
declare @t table(id int,num int)
insert into @t
select 1,32
union all select 2,232
union all select 3,44
union all select 4,72
--创建一个辅助的临时表
declare @count int
select @count = max(num)/40 + 1 from @t
set rowcount @count
select id=identity(int,0,1) into #t from sysobjects
set rowcount 0
--得到查询结果
select a.id
,num=case when a.num<(b.id+1)*40 then a.num-b.id*40 else 40 end
from @t a join #t b on a.num>=b.id*40
order by a.id,num desc
go
drop table #t
/*
id num
----------- -----------
1 32
2 40
2 40
2 40
2 40
2 40
2 32
3 40
3 4
4 40
4 32