求一句SQL,关于数量分配，谢谢

i9998 2009-02-24 09:55:48



求sql语句思路都行，谢谢路过的大侠!

有一个表 

id   group_id   name   value

1    1          A      100

2    1          B      50

3    1          C      -70

4    1          D      -130

5    2          E      30

6    2          F      150

7    2          G      -50

8    2          H      -100

9    2          I      -200

.....



想得到如下结果, adjust_value 是同一group_id 逐步可以扣的数量(value)， 谢谢

name1  value1   name2  value2  adjust_value

A      100      C      -70     70   /*A 有100，够C扣70，A还剩下30，可以给D*/

A      100      D      -130    30   /*A扣完C多的30，给D了*/

B      50       D      -130    50   /*B只有50，全给D了*/

E      30       G      -50     30   /*不同group了,E只有30全给G,还是不够，差20，只能从F拿*/

F      150      G      -50     20   /*...差不多就是这逻辑了*/

F      150      H      -100    100

F      150      I      -200    30



附上建表语句

create table #t(

  id int identity(1,1),

  group_id int,

  name nvarchar(02),

  value int

)

Go

Insert into #t(group_id,name,value) select 1,'A',100

Insert into #t(group_id,name,value) select 1,'B',50

Insert into #t(group_id,name,value) select 1,'C',-70

Insert into #t(group_id,name,value) select 1,'D',-130

Insert into #t(group_id,name,value) select 2,'E',30

Insert into #t(group_id,name,value) select 2,'F',150

Insert into #t(group_id,name,value) select 2,'G',-50

Insert into #t(group_id,name,value) select 2,'H',-100

Insert into #t(group_id,name,value) select 2,'I',-200

GO

...全文

251 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

一直活在明天 2009-11-09

打赏
举报

学习

hanjoe109 2009-07-20

打赏
举报

5樓太強了，隻能大概明白

lzfrab 2009-02-25

打赏
举报

学习..

htl258_Tony 2009-02-25

打赏
举报

[Quote=引用 7 楼 dawugui 的回复:]
引用 6 楼 Haiwer 的回复:
绕了半天，呵呵
我顶.
[/Quote]..

dawugui 2009-02-24

打赏
举报

[Quote=引用 6 楼 Haiwer 的回复:]
绕了半天，呵呵
[/Quote]我顶.

昵称被占用了 2009-02-24

打赏
举报

绕了半天，呵呵

昵称被占用了 2009-02-24

打赏
举报

create table #t(

  id int identity(1,1),

  group_id int,

  name nvarchar(02),

  value int

)

Go

Insert into #t(group_id,name,value) select 1,'A',100

Insert into #t(group_id,name,value) select 1,'B',50

Insert into #t(group_id,name,value) select 1,'C',-70

Insert into #t(group_id,name,value) select 1,'D',-130

Insert into #t(group_id,name,value) select 2,'E',30

Insert into #t(group_id,name,value) select 2,'F',150

Insert into #t(group_id,name,value) select 2,'G',-50

Insert into #t(group_id,name,value) select 2,'H',-100

Insert into #t(group_id,name,value) select 2,'I',-200

GO



select a.name as name1

,a.value as value1

,b.name as name2

,b.value as value2

,case when a.v2+b.V2<=0 then case when a.v2+b.V1<=a.value then a.v2+b.V1 else a.value end

else case when -(a.v1+b.V2)<= -b.value then -(a.v1+b.V2) else -b.value end

end as adjust_value

from ( 

select *,isnull((select sum(value) from #t where group_id = t.group_id and name< t.name and value>0),0) as V1

,(select sum(value) from #t where group_id = t.group_id and name<= t.name and value>0) as V2

from #t t

where value>0

) as a,(

select *,isnull((select sum(value) from #t where group_id = t.group_id and name< t.name and value<0),0) as V1

,(select sum(value) from #t where group_id = t.group_id and name<= t.name and value<0) as V2

from #t t

where value<0

) as b

where a.group_id=b.group_id 

and a.V2+b.V1>=0 and a.V1+b.V2<=0

order by a.name,b.name



--结果

group_id    name1 value1      name2 value2      adjust_value 

----------- ----- ----------- ----- ----------- ------------ 

1           A     100         C     -70         70

1           A     100         D     -130        30

1           B     50          D     -130        50

2           E     30          G     -50         30

2           F     150         G     -50         20

2           F     150         H     -100        100

2           F     150         I     -200        30



（所影响的行数为 7 行）