根据汇总数量依次扣减，以下写法属于sql2012以上版本的新特性，2012以下版本都不支持此写法。看来新版本确实为我们节省不省事。
1 ;WITH a AS( 2 SELECT 'A1' billno,2400 total 3 UNION ALL 4 SELECT 'A3' billno,4000 total 5 UNION ALL 6 SELECT 'A5' billno,7000 total 7 ) 8 , b AS( 9 SELECT *,ROW_NUMBER() OVER(PARTITION BY dtl.billno ORDER BY dtl.billno) rowid FROM ( 10 SELECT 'A1' billno,100 qty , 0 fqty 11 UNION ALL 12 SELECT 'A1' billno,500 qty, 0 fqty 13 UNION ALL 14 SELECT 'A1' billno,1500 qty, 0 fqty 15 UNION ALL 16 SELECT 'A1' billno,330 qty, 0 fqty 17 UNION ALL 18 SELECT 'A3' billno,700 qty, 0 fqty 19 UNION ALL 20 SELECT 'A3' billno,95 qty, 0 fqty 21 UNION ALL 22 SELECT 'A5' billno,780 qty, 0 fqty 23 ) dtl 24 ) 25 --以下只支持sql2012以上版本 26 SELECT t1.billno,t1.qty,a.total, a.total -t1.qtyresult result 27 FROM (SELECT *,SUM(b.qty) OVER(PARTITION BY b.billno ORDER BY b.rowid ROWS UNBOUNDED PRECEDING) AS qtyresult FROM b) t1 LEFT JOIN a ON a.billno=t1.billno
结果图如下：
