请指教一个SQL语句 关于行与行计算

Question

现有表如下：  编号 委托量 完成量 日期
1      10     2    2009-3-1
1      10     9    2009-3-2   
2      20     15   2009-3-1
2      20     5    2009-3-3
3      10     14   2009-3-2
4      15     12   2009-3-3我想变成：编号 委托量 完成量 有效量 日期
1      10     2      2     2009-3-1
1      8      9      8     2009-3-2
2      20     15     15    2009-3-1
2      5      5      5     2009-3-3
3      10     14     10    2009-3-2
4      15     12     12    2009-3-3也就是计算出每天剩余的委托量及有效量。请问该怎么select啊？

Answer 1

create table tb(编号 int,委托量 int,完成量 int,日期 datetime)
insert into tb values(1 ,     10 ,   2  ,  '2009-3-1') 
insert into tb values(1 ,     10 ,   9  ,  '2009-3-2')  
insert into tb values(2 ,     20 ,   15 ,  '2009-3-1') 
insert into tb values(2 ,     20 ,   5  ,  '2009-3-3') 
insert into tb values(3 ,     10 ,   14 ,  '2009-3-2') 
insert into tb values(4 ,     15 ,   12 ,  '2009-3-3')
goselect * , case when 完成量 > 委托量 then 委托量 else 完成量 end as 有效量 from
(
select 编号 ,
       委托量 = (case when (select sum(完成量) from tb where 编号 = t.编号 and 日期 < t.日期) is null then 委托量 
                      else (select top 1 委托量 from tb where 编号 = t.编号 order by 日期)- (select sum(完成量) from tb where 编号 = t.编号 and 日期 < t.日期) end),
       完成量 ,
       日期
from tb t
) mdrop table tb /*
编号          委托量         完成量         日期                                                     有效量         
----------- ----------- ----------- ------------------------------------------------------ ----------- 
1           10          2           2009-03-01 00:00:00.000                                2
1           8           9           2009-03-02 00:00:00.000                                8
2           20          15          2009-03-01 00:00:00.000                                15
2           5           5           2009-03-03 00:00:00.000                                5
3           10          14          2009-03-02 00:00:00.000                                10
4           15          12          2009-03-03 00:00:00.000                                12（所影响的行数为 6 行）
*/

Answer 2

create table tb(编号 int,委托量 int,完成量 int,日期 datetime)
insert into tb values(1 ,     10 ,   2  ,  '2009-3-1') 
insert into tb values(1 ,     10 ,   9  ,  '2009-3-2')  
insert into tb values(2 ,     20 ,   15 ,  '2009-3-1') 
insert into tb values(2 ,     20 ,   5  ,  '2009-3-3') 
insert into tb values(3 ,     10 ,   14 ,  '2009-3-2') 
insert into tb values(4 ,     15 ,   12 ,  '2009-3-3')
goselect 编号, 委托量-isnull((select sum(完成量) from tb where 编号=a.编号 and 日期<a.日期),0) 委托量,
完成量, 
有效量=case when (委托量-isnull((select sum(完成量) from tb where 编号=a.编号 and 日期<a.日期),0))<完成量 then  委托量-isnull((select sum(完成量) from tb where 编号=a.编号 and 日期<a.日期),0) else 完成量 end,
日期 from tb a drop table tb
/*
编号          委托量         完成量         有效量         日期                                                     
----------- ----------- ----------- ----------- ------------------------------------------------------ 
1           10          2           2           2009-03-01 00:00:00.000
1           8           9           8           2009-03-02 00:00:00.000
2           20          15          15          2009-03-01 00:00:00.000
2           5           5           5           2009-03-03 00:00:00.000
3           10          14          10          2009-03-02 00:00:00.000
4           15          12          12          2009-03-03 00:00:00.000（所影响的行数为 6 行）*/

Answer 3

create table tb(编号 int,委托量 int,完成量 int,日期 datetime)
insert into tb values(1 ,     10 ,   2  ,  '2009-3-1') 
insert into tb values(1 ,     10 ,   9  ,  '2009-3-2')  
insert into tb values(2 ,     20 ,   15 ,  '2009-3-1') 
insert into tb values(2 ,     20 ,   5  ,  '2009-3-3') 
insert into tb values(3 ,     10 ,   14 ,  '2009-3-2') 
insert into tb values(4 ,     15 ,   12 ,  '2009-3-3')
goselect 编号, 委托量-isnull((select sum(完成量) from tb where 编号=a.编号 and 日期<a.日期),0) 委托量,
完成量, 
有效量=case when (委托量-isnull((select sum(完成量) from tb where 编号=a.编号 and 日期<a.日期),0))<完成量 then  委托量-isnull((select sum(完成量) from tb where 编号=a.编号 and 日期<a.日期),0) else 完成量 end,
日期 from tb a drop table tb
/*
编号          委托量         完成量         有效量         日期                                                     
----------- ----------- ----------- ----------- ------------------------------------------------------ 
1           10          2           2           2009-03-01 00:00:00.000
1           8           9           8           2009-03-02 00:00:00.000
2           20          15          15          2009-03-01 00:00:00.000
2           5           5           5           2009-03-03 00:00:00.000
3           10          14          10          2009-03-02 00:00:00.000
4           15          12          12          2009-03-03 00:00:00.000（所影响的行数为 6 行）*/

Answer 4

SELECT 
编号,
ABS(委托量 - ISNULL((SELECT SUM(完成量) FROM 表 WHERE 编号 = A.编号 AND 日期 < A.日期 GROUP BY 编号),0))AS '委托量',
完成量 AS '完成量',
CASE 
WHEN ABS(委托量 - ISNULL((SELECT SUM(完成量) FROM 表 WHERE 编号 = A.编号 AND 日期 < A.日期 GROUP BY 编号),0)) < 完成量 THEN
ABS(委托量 - ISNULL((SELECT SUM(完成量) FROM 表 WHERE 编号 = A.编号 AND 日期 < A.日期 GROUP BY 编号),0))
ELSE
完成量
END AS '有效量',
日期
FROM
表 A
GROUP BY A.编号,A.委托量,A.完成量,A.日期
ORDER BY A.编号,A.日期

Answer 5

create table a (ida int,wtl int,wcl int ,rp datetime)
insert a select 1,10,2,'2009-03-25' union all
select 2,10,14,'2009-03-25' union all
select 3,20,15,'2009-03-25' union all
select 4,20,20,'2009-03-25' union all
select 5,20,0,'2009-03-25' select ida,wtl,wcl,yxl=
case when wcl<=wtl then wcl
     else wtl end,
rp
from adrop table a
ida     wtl    wcl     yxl     rp
1 10 2 2 2009-03-25 00:00:00.000
2 10 14 10 2009-03-25 00:00:00.000
3 20 15 15 2009-03-25 00:00:00.000
4 20 20 20 2009-03-25 00:00:00.000
5 20 0 0 2009-03-25 00:00:00.000