--创建一个辅助的临时表
select top 3 id=identity(int,0,1) into #t from sysobjects a,sysobjects b--得到查询结果
select a.id
,a.num
,case when a.num<(b.id+1)*40 then a.num-b.id*40 else 40 end
from 表 a join #t b on a.num>=b.id*40
order by a.id--删除临时表
drop table #t
select top 3 id=identity(int,0,1) into #t from sysobjects a,sysobjects b--得到查询结果
select a.id
,a.num
,case when a.num<(b.id+1)*40 then a.num-b.id*40 else 40 end
from 表 a join #t b on a.num>=b.id*40
order by a.id--删除临时表
drop table #t
select top 3 id=identity(int,0,1) into #t from sysobjects a,sysobjects b--得到查询结果
select a.id
,num=case when a.num<(b.id+1)*40 then a.num-b.id*40 else 40 end
from 表 a join #t b on a.num>=b.id*40
order by a.id--删除临时表
drop table #t
select top 3 id=identity(int,0,1) into #t from sysobjects a,sysobjects b--得到查询结果
select a.id
,num=case when a.num<(b.id+1)*40 then a.num-b.id*40 else 40 end
from 表 a join #t b on a.num>=b.id*40
order by a.id,num desc
go--删除临时表
drop table #t
declare @t table(id int,num int)
insert into @t
select 1,32
union all select 2,120
union all select 3,44
union all select 4,72--创建一个辅助的临时表
select top 3 id=identity(int,0,1) into #t from sysobjects a,sysobjects b--得到查询结果
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
3 40
3 4
4 40
4 32(所影响的行数为 8 行)
--*/
--测试数据
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(所影响的行数为 11 行)
*/
改成这样:declare @m int
select @m=max(num) / 40 +1 from @t
set rowcount @m
select id=identity(int,0,1) into #t from sysobjects a,sysobjects b
set rowcount 0
insert into #1 values(1,32)
insert into #1 values(2,120)
insert into #1 values(3,44)
insert into #1 values(4,72)select * into #2 from #1 where num>40while exists (select * from #2 where num>40)
begin
insert into #2 select id,40 as num from #2 where num>40
update #2 set num=num-40 where num>40
endselect * from #2
union all
select * from #1 where num<=40
order by id,num desc
select id=identity(int,0,1) into #t from sysobjects a,sysobjects b--不然可能处理有些数据不能正确被处理的情况.
发现写法还有一个漏洞!
改一下!declare @count int
select @count = ceiling(max(num) * 1.0 /40) from @t
set rowcount @count 还有Jianli2004(健力) 的方法也不错!