这么用太错了。数据一多,速度会慢得吓死你。
IF EXISTS (SELECT * FROM SysObjects WHERE id = object_id(N'[dbo].[fa_Q_LJZJB]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP Procedure [dbo].[fa_Q_LJZJB]
GO
Create Proc fa_Q_LJZJB(@iLevel1 int, @iLevel2 int, @iPeriod int, @sDept VARCHAR(50)) AS
------------ 生成临时表 ----------------
SELECT '1' AS sSort, 1 AS iLevel, fa_AssetTypes.sNum1 AS Num,
Sum(fa_Total.dblYearValue) AS dblYearValueOfSum,
Sum(fa_Total.dblValue) AS dblValueOfSum,
Sum(fa_Total.dblYearDeprTotal) AS dblYearDeprTotalOfSum,
Sum(fa_Total.dblDeprTotal) AS dblDeprTotalOfSum,
Sum([dblDeprTotal])-Sum([dblYearDeprTotal]) AS dblDeprThisYear,
Sum(fa_Total.dblLastAccrualValue) AS dblLastAccrualValueOfSum,
Sum(fa_Total.dblLastDepr) AS dblLastDeprOfSum,
Sum(fa_Total.dblLastAddValue) AS dblLastAddValueOfSum,
Sum(fa_Total.dblLastDecValue) AS dblLastDecValueOfSum,
Sum(fa_Total.dblAccrualValue) AS dblAccrualValueOfSum,
Sum(fa_Total.dblDepr) AS dblDeprOfSum
INTO #fa_Q_LBZJHZ_Pre
FROM fa_Total LEFT JOIN fa_AssetTypes ON fa_Total.sTypeNum = fa_AssetTypes.sNum
WHERE (((fa_Total.sDeptNum) Like @sDept + '%') AND ((fa_Total.iPeriod)=@iPeriod) AND ((fa_AssetTypes.iLevel)>=1))
GROUP BY fa_AssetTypes.sNum1
UNION ALL
SELECT '1' AS sSort, 2 AS iLevel, fa_AssetTypes.sNum1 + fa_AssetTypes.sNum2 AS Num, Sum(fa_Total.dblYearValue) AS dblYearValueOfSum, Sum(fa_Total.dblValue) AS dblValueOfSum, Sum(fa_Total.dblYearDeprTotal) AS dblYearDeprTotalOfSum, Sum(fa_Total.dblDeprTotal) AS dblDeprTotalOfSum, Sum([dblDeprTotal])-Sum([dblYearDeprTotal]) AS dblDeprThisYear, Sum(fa_Total.dblLastAccrualValue) AS dblLastAccrualValueOfSum, Sum(fa_Total.dblLastDepr) AS dblLastDeprOfSum, Sum(fa_Total.dblLastAddValue) AS dblLastAddValueOfSum, Sum(fa_Total.dblLastDecValue) AS dblLastDecValueOfSum, Sum(fa_Total.dblAccrualValue) AS dblAccrualValueOfSum, Sum(fa_Total.dblDepr) AS dblDeprOfSum
FROM fa_Total LEFT JOIN fa_AssetTypes ON fa_Total.sTypeNum = fa_AssetTypes.sNum
WHERE (((fa_Total.iPeriod)=@iPeriod) AND ((fa_AssetTypes.iLevel)>=2) AND ((fa_Total.sDeptNum) Like @sDept + '%'))
GROUP BY fa_AssetTypes.sNum1 + fa_AssetTypes.sNum2
UNION ALL
SELECT '1' AS sSort, 3 AS iLevel, fa_AssetTypes.sNum1 + fa_AssetTypes.sNum2 + fa_AssetTypes.sNum3 AS Num, Sum(fa_Total.dblYearValue) AS dblYearValueOfSum, Sum(fa_Total.dblValue) AS dblValueOfSum, Sum(fa_Total.dblYearDeprTotal) AS dblYearDeprTotalOfSum, Sum(fa_Total.dblDeprTotal) AS dblDeprTotalOfSum, Sum([dblDeprTotal])-Sum([dblYearDeprTotal]) AS dblDeprThisYear, Sum(fa_Total.dblLastAccrualValue) AS dblLastAccrualValueOfSum, Sum(fa_Total.dblLastDepr) AS dblLastDeprOfSum, Sum(fa_Total.dblLastAddValue) AS dblLastAddValueOfSum, Sum(fa_Total.dblLastDecValue) AS dblLastDecValueOfSum, Sum(fa_Total.dblAccrualValue) AS dblAccrualValueOfSum, Sum(fa_Total.dblDepr) AS dblDeprOfSum
FROM fa_Total LEFT JOIN fa_AssetTypes ON fa_Total.sTypeNum = fa_AssetTypes.sNum
WHERE (((fa_Total.sDeptNum) Like @sDept + '%') AND ((fa_Total.iPeriod)=@iPeriod) AND ((fa_AssetTypes.iLevel)>=3))
GROUP BY fa_AssetTypes.sNum1 + fa_AssetTypes.sNum2 + fa_AssetTypes.sNum3
UNION ALL
SELECT '1' AS sSort, 4 AS iLevel, fa_AssetTypes.sNum1 + fa_AssetTypes.sNum2 + fa_AssetTypes.sNum3 + fa_AssetTypes.sNum4 AS Num, Sum(fa_Total.dblYearValue) AS dblYearValueOfSum, Sum(fa_Total.dblValue) AS dblValueOfSum, Sum(fa_Total.dblYearDeprTotal) AS dblYearDeprTotalOfSum, Sum(fa_Total.dblDeprTotal) AS dblDeprTotalOfSum, Sum([dblDeprTotal])-Sum([dblYearDeprTotal]) AS dblDeprThisYear, Sum(fa_Total.dblLastAccrualValue) AS dblLastAccrualValueOfSum, Sum(fa_Total.dblLastDepr) AS dblLastDeprOfSum, Sum(fa_Total.dblLastAddValue) AS dblLastAddValueOfSum, Sum(fa_Total.dblLastDecValue) AS dblLastDecValueOfSum, Sum(fa_Total.dblAccrualValue) AS dblAccrualValueOfSum, Sum(fa_Total.dblDepr) AS dblDeprOfSum
FROM fa_Total LEFT JOIN fa_AssetTypes ON fa_Total.sTypeNum = fa_AssetTypes.sNum
WHERE (((fa_Total.sDeptNum) Like @sDept + '%') AND ((fa_Total.iPeriod)=@iPeriod) AND ((fa_AssetTypes.iLevel)>=4))
GROUP BY fa_AssetTypes.sNum1 + fa_AssetTypes.sNum2 + fa_AssetTypes.sNum3 + fa_AssetTypes.sNum4
UNION ALL
SELECT '2' AS sSort, -1 AS iLevel, '' AS Num, Sum(fa_Total.dblYearValue) AS dblYearValueOfSum, Sum(fa_Total.dblValue) AS dblValueOfSum, Sum(fa_Total.dblYearDeprTotal) AS dblYearDeprTotalOfSum, Sum(fa_Total.dblDeprTotal) AS dblDeprTotalOfSum, Sum([dblDeprTotal])-Sum([dblYearDeprTotal]) AS dblDeprThisYear, Sum(fa_Total.dblLastAccrualValue) AS dblLastAccrualValueOfSum, Sum(fa_Total.dblLastDepr) AS dblLastDeprOfSum, Sum(fa_Total.dblLastAddValue) AS dblLastAddValueOfSum, Sum(fa_Total.dblLastDecValue) AS dblLastDecValueOfSum, Sum(fa_Total.dblAccrualValue) AS dblAccrualValueOfSum, Sum(fa_Total.dblDepr) AS dblDeprOfSum
FROM fa_Total LEFT JOIN fa_AssetTypes ON fa_Total.sTypeNum = fa_AssetTypes.sNum
WHERE (((fa_Total.sDeptNum) Like @sDept + '%') AND ((fa_Total.iPeriod)=@iPeriod) AND ((fa_AssetTypes.iLevel) >= @iLevel1))
------------ 生成临时表 END ----------------
SELECT (CASE WHEN [Num]='' THEN ' 总 计 ' ELSE Space(([fa_AssetTypes].[iLevel]-@iLevel1)*4) + [sName] + '(' + [Num] + ')' END) AS Name,
#fa_Q_LBZJHZ_Pre.dblYearValueOfSum, #fa_Q_LBZJHZ_Pre.dblValueOfSum, #fa_Q_LBZJHZ_Pre.dblYearDeprTotalOfSum,
#fa_Q_LBZJHZ_Pre.dblDeprTotalOfSum, #fa_Q_LBZJHZ_Pre.dblDeprOfSum
FROM #fa_Q_LBZJHZ_Pre LEFT JOIN fa_AssetTypes ON #fa_Q_LBZJHZ_Pre.Num = fa_AssetTypes.sNum
WHERE (((#fa_Q_LBZJHZ_Pre.iLevel) Between @iLevel1 And @iLevel2)) OR (((#fa_Q_LBZJHZ_Pre.sSort)='2'))
ORDER BY #fa_Q_LBZJHZ_Pre.sSort, #fa_Q_LBZJHZ_Pre.Num
GO