求教一个sql平均拆分数据问题 ？

Question

原数据：
id number quantity
1 AAAA 9000
2 BBBB 1000
3 CCCC 2000
4 DDDD 3000
5 EEEE 4500
6 FFFF 4500以上数据能否可以通过SQL查询后显示成下面：（region例是按quantity汇总后平均分成8份，AAAA占3份，显示1-2-3；BBBB，CCCC合占一份，显示4，...）
需要数据：
id number quantity region
1 AAAA 9000 1-2-3
2 BBBB 1000 4
3 CCCC 2000 4
4 DDDD 3000 5
5 EEEE 4500 6-7
6 FFFF 4500 7-8请各高手帮个忙，给个解决方法。

Answer 1

你这个需求怎么来的?region例是按quantity汇总后平均分成8份，AAAA占3份，显示1-2-3；BBBB，CCCC合占一份，显示4，...

Answer 2

5 EEEE 4500 6-7
6 FFFF 4500 7-8
为什么是这样？ 这个应该是1.5份

Answer 3

create table tb(id int,number varchar(10),quantity int)
insert into tb select 1,'AAAA',9000
insert into tb select 2,'BBBB',1000
insert into tb select 3,'CCCC',2000
insert into tb select 4,'DDDD',3000
insert into tb select 5,'EEEE',4500
insert into tb select 6,'FFFF',4500
go
;with c1 as(
select *,(select SUM(quantity)/8 from tb)q  from tb
),c2 as(
select *,1 as r,convert(varchar(10),'1') as region,quantity-q q2 from c1 where id=1
union all
select id,number,quantity,q,r+1,convert(varchar(10),region+'-'+convert(varchar,r+1)),q2-q from c2 where q2>0
union all
select a.id,a.number,a.quantity,a.q,b.r+1,CONVERT(varchar(10),b.r+1),a.quantity-a.q from c1 a inner join c2 b on a.id=b.id+1 and b.q2=0
union all
select a.id,a.number,a.quantity,a.q,b.r,b.region,b.q2+a.quantity from c1 a inner join c2 b on a.id=b.id+1 and b.q2<0
)select id,number,quantity,region from c2 a where not exists(select 1 from c2 where id=a.id and region>a.region)
/*
id          number     quantity    region
----------- ---------- ----------- ----------
1           AAAA       9000        1-2-3
2           BBBB       1000        4
3           CCCC       2000        4
4           DDDD       3000        5
5           EEEE       4500        6-7
6           FFFF       4500        6-7-8(6 行受影响)*/
go
drop table tb

Answer 4

修正:
create table tb(id int,number varchar(10),quantity int)
insert into tb select 1,'AAAA',9000
insert into tb select 2,'BBBB',1000
insert into tb select 3,'CCCC',2000
insert into tb select 4,'DDDD',3000
insert into tb select 5,'EEEE',4500
insert into tb select 6,'FFFF',4500
go
;with c1 as(
select *,(select SUM(quantity)/8 from tb)q  from tb
),c2 as(
select *,1 as r,convert(varchar(10),'1') as region,quantity-q q2 from c1 where id=1
union all
select id,number,quantity,q,r+1,convert(varchar(10),region+'-'+convert(varchar,r+1)),q2-q from c2 where q2>0
union all
select a.id,a.number,a.quantity,a.q,b.r+1,CONVERT(varchar(10),b.r+1),a.quantity-a.q from c1 a inner join c2 b on a.id=b.id+1 and b.q2=0
union all
select a.id,a.number,a.quantity,a.q,b.r,CONVERT(varchar(10),b.r),b.q2+a.quantity from c1 a inner join c2 b on a.id=b.id+1 and b.q2<0
)select id,number,quantity,region from c2 a where not exists(select 1 from c2 where id=a.id and region>a.region)
/*
id          number     quantity    region
----------- ---------- ----------- ----------
1           AAAA       9000        1-2-3
2           BBBB       1000        4
3           CCCC       2000        4
4           DDDD       3000        5
5           EEEE       4500        6-7
6           FFFF       4500        7-8(6 行受影响)*/
go
drop table tb

Answer 5

很多谢 qianjin036a  的帮忙，忘记说我使用的是SQL2000，请再帮我修改一下，多谢！

Answer 6

因为EEEE FFFF 合计为9000 每一份为3000 所以需分到 6 7 8 三个区域内 7区有EEEE 的0.5份，还有0.5份分到8区; 多谢！

Answer 7

sql2000要用临时表或表变量处理
写法效率不高，直接计算最大倍数，比较有效

Answer 8

--> --> (Roy)生成測試數據
set nocount on;
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[number] nvarchar(4),[quantity] int)
Insert #T
select 1,N'AAAA',9000 union all
select 2,N'BBBB',1000 union all
select 3,N'CCCC',2000 union all
select 4,N'DDDD',3000 union all
select 5,N'EEEE',4500 union all
select 6,N'FFFF',4500
Go
if OBJECT_ID('Tempdb..#1') is not null
drop table #1
select b.*,ceiling([quantity]/a.avgQty) as row,
ceiling((select SUM([quantity]) from #T where ID<=b.ID)/a.avgQty) row2,
cast(null as nvarchar(100)) as region
into #1
from (Select sum([quantity]*1.0)/8 avgQty from #T)a
Cross join #T b while @@ROWCOUNT>0
update #1 set region=isnull(region+'-','')+rtrim(row2-row+1),row=row-1 where row>0select id, number, quantity, region from #1/*
id number quantity region
1 AAAA 9000 1-2-3
2 BBBB 1000 4
3 CCCC 2000 4
4 DDDD 3000 5
5 EEEE 4500 6-7
6 FFFF 4500 7-8
*/

Answer 9

以上SQL2000用循环+临时表实现,SQL2005可以xml生成region合并

Answer 10

问题解决了，多谢roy_88的帮忙！