首先是自定义的一个函数
CREATE FUNCTION GetLastBalanceDate()
RETURNS Datetime AS
BEGIN
declare @Date datetime
select @Date=max(BillDate) from Balance
if @Date is null
set @Date= '2007-1-31 '
Return @Date
END
然后类似这样的视图多个:
CREATE VIEW dbo.VPayment
--本视图用于表示与供应商之间发生的应付加减情况
AS--收货 <现金付款除外>
SELECT MainSeq, '收货 ' MeNO,SignDate BillDate,CustomerNO,Amt from StoreMain where BillType=1 and IsCash=0
UNION ALL--减去退货金额,由于存储的是负数,直接相加 <除掉现金退货单据>
SELECT MainSeq, '退货 ',SignDate,CustomerNO,Amt from StoreMain where BillType=2 and IsCash=0
UNION ALL--减去结帐以后的付款
SELECT SeqNo, '付款 ',BillDate,CustomerNO,-Amt from Payment
UNION ALL--减去结帐以后店面直接汇款给供应商的
SELECT SeqNO, '直接汇款 ',BillDate,CustomerNO,-Amt from RecPayment where IsTran=1
UNION ALL--加上采购点的公摊费用
SELECT SeqNO, '公摊费用 ',BillDate,UnderTaker,Amt from FeeStock where IsNULL(UnderTaker,0) <> 0 and IsPool=1
UNION ALL--需要加上非公摊的费用 <现金付款的采购费用不计算在内>
SELECT A.RelateSeq,B.CName,C.SignDate,A.CustomerNO,A.Amt from FeeStock A,FCode B,StoreMain C where ISNULL(A.RelateSeq,0) <> 0
and A.RelateSeq=C.MainSeq and A.BillType=B.Code and B.Type=2 and A.IsPool=0 And A.IsCash=0
-------------最后的引用如下:
CREATE VIEW dbo.VPayable
AS--
select A.CustomerNO,IsNULL(B.Amt,0)+IsNULL(C.Amt,0) Amt
from Customer A
left join Payable B on A.CustomerNO=B.CustomerNO and B.BillDate=DBO.GetLastBalanceDate()
left join
(select CustomerNO,SUM(Amt) Amt from VPayment where BillDate> DBO.GetLastBalanceDate() group by CustomerNO) C
on A.CustomerNO=C.CustomerNO
where A.Type=2
SQL Server似乎在对比每条数据的时候都会去执行一次DBO.GetLastBalanceDate() ,这样一来数据量很大的时候就会很慢甚至超时
------解决方案--------------------
-------------最后的引用改成如下:
CREATE VIEW dbo.VPayable
AS--
Declare payDate datetime
select payDate=DBO.GetLastBalanceDate()
select A.CustomerNO,IsNULL(B.Amt,0)+IsNULL(C.Amt,0) Amt
from Customer A
left join Payable B on A.CustomerNO=B.CustomerNO and B.BillDate=payDate
left join
(select CustomerNO,SUM(Amt) Amt from VPayment where BillDate> payDate group by CustomerNO) C
on A.CustomerNO=C.CustomerNO
where A.Type=2
会不会快一点
------解决方案--------------------
把函数换成下面的子查询试试看：
Isnull(max(BillDate) from Balance, '2007-1-31 ')
再在这个field --CustomerNO上建个index试试
------解决方案--------------------
你改成这样试试
CREATE VIEW dbo.VPayable
AS--
select A.CustomerNO,IsNULL(B.Amt,0)+IsNULL(C.Amt,0) Amt
from Customer A
left join Payable B on a.type=2 and A.CustomerNO=B.CustomerNO and B.BillDate=DBO.GetLastBalanceDate()
left join
(select CustomerNO,SUM(Amt) Amt from VPayment where BillDate> DBO.GetLastBalanceDate() group by CustomerNO) C
on A.CustomerNO=C.CustomerNO