表A的结构和示例数据如下:
classID sumCount
1 100如果需要10等份的话,则按照sumCount/10* (1...10中的一个数) 分别求和;
同理如果需要5等份的话,则按照sumCount/5* (1...5中的一个数) 分别求和;期望结果:(10等份的情况)
classID thisWeek thisCount
1 1 10
1 2 20
1 3 30
1 4 40
..............................
1 9 90
1 10 100
期望结果:(5等份的情况)
classID thisWeek thisCount
1 1 20
1 2 40
1 3 60
1 4 80
1 5 100
分不多了,谢谢;
classID sumCount
1 100如果需要10等份的话,则按照sumCount/10* (1...10中的一个数) 分别求和;
同理如果需要5等份的话,则按照sumCount/5* (1...5中的一个数) 分别求和;期望结果:(10等份的情况)
classID thisWeek thisCount
1 1 10
1 2 20
1 3 30
1 4 40
..............................
1 9 90
1 10 100
期望结果:(5等份的情况)
classID thisWeek thisCount
1 1 20
1 2 40
1 3 60
1 4 80
1 5 100
分不多了,谢谢;
go
create table A( classID int, sumCount int)
insert A select 1, 100 godeclare @i int,@sum int,@s nvarchar(2000)
select @sum=sumCount,@i=sumCount/10 from A
while @i!>@sum
select @s=isnull(@s+' union ','')+'select [col]='+rtrim(@i/10)+',col2='+rtrim(@i),@i=@i+10exec( 'select a.classID,b.col,b.col2 from a cross join ('+@s+') as b order by b.col')
classID col col2
----------- ----------- -----------
1 1 10
1 2 20
1 3 30
1 4 40
1 5 50
1 6 60
1 7 70
1 8 80
1 9 90
1 10 100
go
create table A( classID int, sumCount int)
insert A select 1, 100 godeclare @i int,@sum int,@a int,@b int,@s nvarchar(2000)
select @sum=sumCount,@i=sumCount/5 from A--/10改为5
select @a=@i,@b=1
while @i!>@sum
select @s=isnull(@s+' union ','')+'select [col]='+rtrim(@b)+',col2='+rtrim(@i),@i=@i+@a,@b=@b+1
exec( 'select a.classID,b.col,b.col2 from a cross join ('+@s+') as b order by b.col')
classID col col2
----------- ----------- -----------
1 1 20
1 2 40
1 3 60
1 4 80
1 5 100
go
create table A( classID int, sumCount int)
insert A select 1, 100 godeclare @i int,@sum int,@a int,@b int,@s nvarchar(2000)
select @sum=sumCount,@i=sumCount/10 from A--/10改为5
select @a=@i,@b=1
while @i!>@sum
select @s=isnull(@s+' union ','')+'select [col]='+rtrim(@b)+',col2='+rtrim(@i),@i=@i+@a,@b=@b+1
exec( 'select a.classID,b.col,b.col2 from a cross join ('+@s+') as b order by b.col')classID col col2
----------- ----------- -----------
1 1 10
1 2 20
1 3 30
1 4 40
1 5 50
1 6 60
1 7 70
1 8 80
1 9 90
1 10 100