學習紀錄

透過SUM() OVER 可以省略掉很多子查詢的寫法 .. 且可以很簡易的達到需求

SELECT DISTINCT  B.TB010, MB002,
SUM(B.TB019) over(PARTITION BY B.TB010) Qty,    --計算數量
SUM(B.TB019) over(ORDER BY B.TB010) Acc_Qty ,   --累積數量
SUM(B.TB019) over() Total_Qty                   --總數量
FROM POSTB AS  A
INNER JOIN  POSTB AS B ON A.TB001= B.TB001 AND
A.TB002= B.TB002 AND
A.TB003= B.TB003 AND
A.TB006= B.TB006
INNER JOIN  INVMB ON MB001 = B.TB010
WHERE A.TB010 = '5000001' AND B.TB010  '5000001' --AND B.TB001 ='20180731'
ORDER BY Qty desc

結果