急急急视图问题,万分感谢!!!!(100分)

  • 主题发起人 主题发起人 blhappy
  • 开始时间 开始时间
B

blhappy

Unregistered / Unconfirmed
GUEST, unregistred user!
高手你们好:
由于我的程序有大量的计算,所以在sqlserver建立了大量的视图,我用了大量的视图
例如其中的一个表1的字段如下:工作令号、架号、班次、支数、条数、材料1、材料2、
材料3、批号材料1、批号材料2、批号材料3、日期,由表1生成了几个视图V1按班次
groupby sum(支数),视图V2按架号groupby sum(支数),视图V3按工作令号、班次groupby
sum(支数),再把表1、V1、V2、V3、材料库表连接到一起组成新的视图VV并计算出相应的
结果,以供在delphi中做查询时用到,表2、表3、等许多表计算都是这样利用视图得出的,
做查询程序只要对视图访问就可以了,但是其中有的表N的计算过程中用到其它各视图的结果,
我就把表n经过表1那样的和本身的视图连接以后,又将表N需要的其它视图的结量也通过
对其它表的视图以相同字段的left outer join 连接到了一起,但是点“!”号运行时出
现错误,错误信息如下:a query 256 exceed! 或者是link outtimed!请问各们高手指教
错误的原因,再一个就是做计算量大的程序是怎样处理的,请高手指教,先谢谢了,
我会万分感谢的!
 
视图一般用来解决多表连接、查询和更新的问题,象你这种情况一般用存储过程来解决,
感觉你把视图用错了地方,出现意外就不可避免啦
 
对啊,大量的计算用存储过程才行啊,
 
请高手诉我存储过程是怎么用的,举个例子,万分感谢!
 
这么用太错了。数据一多,速度会慢得吓死你。
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
 
liqunxin:
您好!您给的代码我不太明,请解释一下好吗,谢谢!
 
大家好:
比如我有一个视图V1想通过一个过程来生成一个临时表T1并使这个过程在sql
中第隔一段时间自动执行,请各位高手指教,具体代码怎么写,谢谢各位高手,我
会重金酬谢大家的。
 
后退
顶部