根据汇总数量依次扣减的SQL新语法

根据汇总数量依次扣减，以下写法属于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

 

结果图如下：

转载于:https://www.cnblogs.com/garyxiao/p/8528776.html