使用 CTE， 计算固定资产折旧（直线法）。
DECLARE @Assets TABLE (NAME VARCHAR(20), PurchaseCost MONEY, Period INT)INSERT INTO @AssetsSELECT '计算机', 5000, 24;WITH SLDepSched (AssetID, [Month], Period -- 固定资产,SLDepAmt, SLBookValue, SLCumDep -- 直线法) AS (SELECT NAME, 0, Period,ROUND(PurchaseCost/Period, 2)-- 直线法折旧额,PurchaseCost, CAST(0 AS MONEY)FROM @AssetsUNION ALLSELECT AssetID, [Month]+1, Period,CASE [Month]+1 WHEN Period THEN SLBookValue ELSE SLDepAmt END,CASE [Month]+1 WHEN Period THEN CAST(0 AS MONEY) ELSE SLBookValue - SLDepAmt END,CASE [Month]+1 WHEN Period THEN SLCumDep + SLBookValue ELSE SLCumDep + SLDepAmt ENDFROM SLDepSchedWHERE [Month] < Period)SELECT AssetID, [Month], SLDepAmt, SLBookValue, SLCumDep FROM SLDepSchedORDER BY AssetID, [Month]
计算机0208.335000.000.00
计算机1208.334791.67208.33
计算机2208.334583.34416.66
计算机3208.334375.01624.99
计算机4208.334166.68833.32
计算机5208.333958.351041.65
计算机6208.333750.021249.98
计算机7208.333541.691458.31
计算机8208.333333.361666.64
计算机9208.333125.031874.97
计算机10208.332916.702083.30
计算机11208.332708.372291.63
计算机12208.332500.042499.96
计算机13208.332291.712708.29
计算机14208.332083.382916.62
计算机15208.331875.053124.95
计算机16208.331666.723333.28
计算机17208.331458.393541.61
计算机18208.331250.063749.94
计算机19208.331041.733958.27
计算机8.33833.404166.60
计算机21208.33625.074374.93
计算机22208.33416.744583.26
计算机23208.33208.414791.59
计算机24208.410.005000.00
