1樓：黃贇
已知：每個月的銷售額資料
求解：截止到當前季度的累計銷售額
方法一：遞迴方式
方法二：不對等 Join 方式
方式三：視窗函式 Window Function
CREATE TABLE dbo.FctSales
ORDER_YEAR INT
, ORDER_MONTH INT
, AMOUNT INT
GO INSERT INTO dbo.FctSales(ORDER_YEAR,ORDER_MONTH,AMOUNT) VALUES(2017,1,100)
INSERT INTO dbo.FctSales(ORDER_YEAR,ORDER_MONTH,AMOUNT) VALUES(2017,2,200)
INSERT INTO dbo.FctSales(ORDER_YEAR,ORDER_MONTH,AMOUNT) VALUES(2017,3,300)
INSERT INTO dbo.FctSales(ORDER_YEAR,ORDER_MONTH,AMOUNT) VALUES(2017,4,400)
INSERT INTO dbo.FctSales(ORDER_YEAR,ORDER_MONTH,AMOUNT) VALUES(2017,5,500)
INSERT INTO dbo.FctSales(ORDER_YEAR,ORDER_MONTH,AMOUNT) VALUES(2017,6,600)
INSERT INTO dbo.FctSales(ORDER_YEAR,ORDER_MONTH,AMOUNT) VALUES(2017,7,700)
INSERT INTO dbo.FctSales(ORDER_YEAR,ORDER_MONTH,AMOUNT) VALUES(2017,8,800)
INSERT INTO dbo.FctSales(ORDER_YEAR,ORDER_MONTH,AMOUNT) VALUES(2017,9,900)
INSERT INTO dbo.FctSales(ORDER_YEAR,ORDER_MONTH,AMOUNT) VALUES(2017,10,1000)
INSERT INTO dbo.FctSales(ORDER_YEAR,ORDER_MONTH,AMOUNT) VALUES(2017,11,1100)
INSERT INTO dbo.FctSales(ORDER_YEAR,ORDER_MONTH,AMOUNT) VALUES(2017,12,1200)
GO -- 方法一：遞迴方式
;WITH BASE_QUERY AS (
SELECT ORDER_YEAR,
CASE
WHEN ORDER_MONTH IN (1,2,3) THEN 1
WHEN ORDER_MONTH IN (4,5,6) THEN 2
WHEN ORDER_MONTH IN (7,8,9) THEN 3
WHEN ORDER_MONTH IN (10,11,12) THEN 4
END QUART
,SUM(AMOUNT )AS QUART_AMOUNT
FROM FctSales
GROUP BY ORDER_YEAR ,
CASE
WHEN ORDER_MONTH IN (1,2,3) THEN 1
WHEN ORDER_MONTH IN (4,5,6) THEN 2
WHEN ORDER_MONTH IN (7,8,9) THEN 3
WHEN ORDER_MONTH IN (10,11,12) THEN 4
END, BASE_CALC AS (
SELECT ORDER_YEAR, QUART , QUART_AMOUNT AS CURRENT_AMOUNT, QUART_AMOUNT AS TOTAL
FROM BASE_QUERY
WHERE ORDER_YEAR = 2017 AND QUART = 1
UNION ALL
SELECT F.ORDER_YEAR, F.QUART , F.
QUART_AMOUNT AS CURRENT_AMOUNT, F.QUART_AMOUNT + B.TOTAL AS TOTAL
FROM BASE_CALC B
INNER JOIN BASE_QUERY F
ON B.ORDER_YEAR = F.ORDER_YEAR
AND B.QUART = F.QUART - 1
SELECT * FROM BASE_CALC
-- 方法二：不對等 Join 方式
;WITH BASE_QUERY AS (
SELECT ORDER_YEAR,
CASE
WHEN ORDER_MONTH IN (1,2,3) THEN 1
WHEN ORDER_MONTH IN (4,5,6) THEN 2
WHEN ORDER_MONTH IN (7,8,9) THEN 3
WHEN ORDER_MONTH IN (10,11,12) THEN 4
END QUART
,SUM(AMOUNT )AS QUART_AMOUNT
FROM FctSales
GROUP BY ORDER_YEAR ,
CASE
WHEN ORDER_MONTH IN (1,2,3) THEN 1
WHEN ORDER_MONTH IN (4,5,6) THEN 2
WHEN ORDER_MONTH IN (7,8,9) THEN 3
WHEN ORDER_MONTH IN (10,11,12) THEN 4
ENDSELECT CUR.ORDER_YEAR, CUR.QUART, T.TOTAL
FROM BASE_QUERY CUR
FROM BASE_QUERY PRE
WHERE PRE.ORDER_YEAR = CUR.ORDER_YEAR
AND PRE.QUART< =CUR.QUART ) T
--方式三：視窗函式 Window Function
;WITH BASE_QUERY AS (
SELECT ORDER_YEAR,
CASE
WHEN ORDER_MONTH IN (1,2,3) THEN 1
WHEN ORDER_MONTH IN (4,5,6) THEN 2
WHEN ORDER_MONTH IN (7,8,9) THEN 3
WHEN ORDER_MONTH IN (10,11,12) THEN 4
END QUART
,SUM(AMOUNT )AS QUART_AMOUNT
FROM FctSales
GROUP BY ORDER_YEAR ,
CASE
WHEN ORDER_MONTH IN (1,2,3) THEN 1
WHEN ORDER_MONTH IN (4,5,6) THEN 2
WHEN ORDER_MONTH IN (7,8,9) THEN 3
WHEN ORDER_MONTH IN (10,11,12) THEN 4
ENDSELECT ORDER_YEAR
,QUART
,SUM(QUART_AMOUNT) OVER (
PARTITION BY ORDER_YEAR
ORDER BY QUART
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS TOTAL
FROM BASE_QUERY
如何正確分析財務報表？
Vincent C 同時還要看未來，看所處行業，競爭環境等，綜合考慮企業是否能夠在將來扭虧為盈，為投資人帶來回報。關於財務報表分析，寫過一些內容，在這裡做點分享 風中麥田 對於新公司來講，其直接問題在於經營費用過高，或者經營費用不變，銷售收入下降了，導致營業利潤為負數。對於分析存在問題，下面就一層一...
如何判斷財務報表的質量？
知之為知之 財務報表的質量，我認為有兩個關鍵點，其一是其報表的真實性及合規性，這是報表質量的基礎 其二是通過列報反應報表主體產品的盈利能力及發展能力的持續性。 客戶經理的那些事 為了銀行貸款或爭取授信，把財務報告做得漂亮些，迎合銀行的信貸標準，本來已經是老爺們兒，非得弄成小鮮肉！為了應對稅務問題，少...
如何編寫個人財務報表？
北風要遠行 首先，下面一位大佬已經回答非常全面了，我就不碼那麼多字了。你要把自己整個人看做是乙個企業來思考。我就提一點個人看法 1 爸媽給的錢，是實收資本 爸媽，要寫二級明細，哈哈。爸媽是你的投資人，因為看好你，投給你的，以後你是要給予回報的。包括給的生活費，都是給你的投資，應該算做投資人的增資。同...