sql 2条记录共享一个数值并且从上到下满足并分配
本帖最后由 myfanjun 于 2013-2-4 16:15 编辑
idx fname fmodel fqty p sumfqty
1 A1 0.20000 89.00000 K 150.00000
2 A1 0.20000 89.00000 K 150.00000
3 A2 0.50000 20.00000 K1 10.00000
4 A2 0.50000 20.00000 K1 10.00000
根据sumfqty的值（p和fmodel的值一样）
从上到下满足fqty的值，不足下次自动补足
idx fname fmodel fqty p sumfqty
1 A1 0.20000 89.00000 K 89
2 A1 0.20000 89.00000 K 61(不足，还剩下28个就满足了)
3 A2 0.50000 20.00000 K1 10(不足，还剩下10个就满足了)
4 A2 0.50000 20.00000 K1 0
..............................
请问这样的SQL语句怎么写？
[解决办法]
-- data
if object_id('tempdb.dbo.#') is not null drop table #
create table #(idx int, fname varchar(8), fmodel float, fqty float, p varchar(8), sumfqty float)
insert into #
select 1, 'A1', 0.20000, 89.00000, 'K', 150.00000 union all
select 2, 'A1', 0.20000, 89.00000, 'K', 150.00000 union all
select 3, 'A2', 0.50000, 20.00000, 'K1', 10.00000 union all
select 4, 'A2', 0.50000, 20.00000, 'K1', 10.00000
-- query
;with tmp as
(
select *, v=sumfqty-(select isnull(sum(fqty),0) from # where p=t.p and fmodel=t.fmodel and idx<t.idx) from # t
)
select *, case when v>fqty then fqty when v>0 then v else 0 end from tmp
/*
idx fname fmodel fqty p sumfqty v
----------- -------- ---------------------- ---------------------- -------- ---------------------- ---------------------- ----------------------
1 A1 0.2 89 K 150 150 89
2 A1 0.2 89 K 150 61 61
3 A2 0.5 20 K1 10 10 10
4 A2 0.5 20 K1 10 -10 0
*/
