sql数据分配问题

Question

现在有这样的表：
mx     sl
-----------
1001    30
1002    50
3001    15
4010    100
5006    1
6021    1000这个表里存放的是各个客户的订货数量；现在仓库只有200个商品，要按客户号的大小顺序分配。
想要得到的是这样的结果：
mx     sl       fpsl
----------- 
1001    30       30
1002    50       50
3001    15       15
4010    100      5
5006    1        0
6021    1000     0
这里应该是要用到sql的自减的，不会用，求助各位：

Answer 1

create table tb
(mx varchar(10), sl int)
insert tb
select '1001', 30 union all
select '1002', 50 union all
select '3001', 15 union all
select '4010', 100 union all
select '5006', 1 union all
select '6021', 1000
select  a.mx,a.sl,fpsl=(case when 200-sum(isnull(b.sl,0))>=0 then a.sl else 0 end) from tb a left join tb b on a.mx>=b.mx
group by a.mx,a.sl
order by a.mx
/*mx         sl          fpsl        
---------- ----------- ----------- 
1001       30          30
1002       50          50
3001       15          15
4010       100         100
5006       1           1
6021       1000        0（所影响的行数为 6 行）*/

Answer 2

上面那个例子中的分配数量有问题：应该是：
mx sl            fpsl
-------          ----  
1001 30           30
1002 50           50
3001 15           15
4010 100          100
5006 1 0          1 
6021 1000 0         4

Answer 3

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([mx] [int],[sl] [int])
INSERT INTO [tb]
SELECT '1001','30' UNION ALL
SELECT '1002','50' UNION ALL
SELECT '3001','15' UNION ALL
SELECT '4010','100' UNION ALL
SELECT '5006','1' UNION ALL
SELECT '6021','1000'--SELECT * FROM [tb]-->SQL查询如下:
select *,
fpsl=case when (select 200-sum(sl) from tb where mx<=t.mx)>=0 then sl else 0 end
from tb t
/*
mx          sl          fpsl
----------- ----------- -----------
1001        30          30
1002        50          50
3001        15          15
4010        100         100
5006        1           1
6021        1000        0(6 行受影响)
*/

Answer 4

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([mx] [int],[sl] [int])
INSERT INTO [tb]
SELECT '1001','30' UNION ALL
SELECT '1002','50' UNION ALL
SELECT '3001','15' UNION ALL
SELECT '4010','100' UNION ALL
SELECT '5006','1' UNION ALL
SELECT '6021','1000'--SELECT * FROM [tb]-->SQL查询如下:
select *,
fpsl=case when (select 200-sum(sl) from tb where mx<=t.mx)>=0 then sl else sl+(200-(select sum(sl) from tb where mx<=t.mx)) end
from tb t
/*
mx          sl          fpsl
----------- ----------- -----------
1001        30          30
1002        50          50
3001        15          15
4010        100         100
5006        1           1
6021        1000        4(6 行受影响)*/

Answer 5

或者 最后的不够，有多少分多少
select  a.mx,a.sl,
fpsl=(case when 100-sum(isnull(b.sl,0))>=0 then a.sl 
else case when 100+a.sl-sum(isnull(b.sl,0))>0 
then 100+a.sl-sum(isnull(b.sl,0))else 0 end end) from tb a left join tb b on a.mx>=b.mx
group by a.mx,a.sl
order by a.mx
/*
mx         sl          fpsl        
---------- ----------- ----------- 
1001       30          30
1002       50          50
3001       15          15
4010       100         5
5006       1           0
6021       1000        0（所影响的行数为 6 行）*/

Answer 6

select  a.mx,a.sl,
fpsl=(case when 200-sum(isnull(b.sl,0))>=0 then a.sl 
else case when 200+a.sl-sum(isnull(b.sl,0))>0 
then 200+a.sl-sum(isnull(b.sl,0))else 0 end end) from tb a left join tb b on a.mx>=b.mx
group by a.mx,a.sl
order by a.mx
/*
mx         sl          fpsl        
---------- ----------- ----------- 
1001       30          30
1002       50          50
3001       15          15
4010       100         100
5006       1           1
6021       1000        4（所影响的行数为 6 行）*/

Answer 7

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-05-14 15:48:44
-- Version:
--      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
-- Oct 14 2005 00:33:37 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb([mx] int,[sl] int)
insert #tb
select 1001,30 union all
select 1002,50 union all
select 3001,15 union all
select 4010,100 union all
select 5006,1 union all
select 6021,1000
--------------开始查询--------------------------
declare @v int 
set @v=150
select mx,sl,case when 
case when num<@v then sl else @v-(num-sl) end>0 
then
 case when num<@v then sl else @v-(num-sl) end
 else 0 end num
from 
(
select *,
(select sum([sl]) from #tb where  [mx]<=t.[mx]) as num 
from #tb  t
) t
----------------结果----------------------------
/* (6 行受影响)
mx          sl          num
----------- ----------- -----------
1001        30          30
1002        50          50
3001        15          15
4010        100         55
5006        1           0
6021        1000        0(6 行受影响)
*/