我现在就是写的视图,速度好慢
---------------------------------------------------------------------
CREATE view vACCPMBPay_Com
as
--采购单(审核+已入库+余额大于0)
select substring(a.PUR001,1,2) INV_Class,A.PUR001 INV_ID,H.INV003 INV_NAME,A.PUR002 INV_NO,A.PUR003 INV_DATE,A.PUR004 INV_OBJID,
D.CBA001 INV_OBJNO,D.CBA002 INV_OBJNAME,A.PUR005 INV_RELID,A.PUR014 INV_OPDEPTNO,F.DPA002 INV_OPDEPTNAME,A.PUR015 INV_OPUSERID,
C.EPA002 INV_OPUSERNO,C.EPA003 INV_OPUSERNAME,(A.PUR018+A.PUR031) INV_SUM,(A.PUR018+A.PUR031-A.PUR020) INV_LASTSUM,
0 INV_GAIN,0 INV_TICHEN,A.PUR032 INV_REMARK,A.PUR008 INV_PAYNO,E.PWZ002 INV_PAYNAME,A.PUR009 INV_PAYDAYS,
datediff(day,dateadd(day,A.PUR009,A.PUR003),getdate()) INV_DAYS,dateadd(day,A.PUR009,A.PUR003) INV_PAYEND,'65' INV_SIGN,A.PUR024 INV_ACCSIGN,INV_ACCSIGNNAME=CASE A.PUR024
WHEN '0' THEN '未记账'
WHEN '1' THEN '已记账' ELSE '无需记账' END
from PURPUR A
INNER JOIN CMSINV H ON A.PUR001=H.INV002
INNER JOIN CUSCBA D ON A.PUR004=D.CBA000
INNER JOIN CMSEPA C ON A.PUR015=C.EPA001
LEFT JOIN CMSDPA F ON A.PUR014=F.DPA001
LEFT JOIN JXCPWZ E ON A.PUR008=E.PWZ001
WHERE A.PUR022='3' and (A.PUR026<>'0') and ((A.PUR018+A.PUR031-A.PUR020)>0)
union all
--销售退货单(审核+入库+余额大于0)
select substring(a.SBK001,1,2) INV_Class,A.SBK001 INV_ID,H.INV003 INV_NAME,A.SBK002 INV_NO,A.SBK003 INV_DATE,A.SBK004 INV_OBJID,
D.CBA001 INV_OBJNO,D.CBA002 INV_OBJNAME,A.SBK005 INV_RELID,A.SBK011 INV_OPDEPTNO,F.DPA002 INV_OPDEPTNAME,A.SBK012 INV_OPUSERID,
C.EPA002 INV_OPUSERNO,C.EPA003 INV_OPUSERNAME,A.SBK014 INV_SUM,(A.SBK014-A.SBK018) INV_LASTSUM,
(A.SBK015-A.SBK034-A.SBK016) INV_GAIN,A.SBK036 INV_TICHEN,A.SBK032 INV_REMARK,A.SBK008 INV_PAYNO,
E.PWZ002 INV_PAYNAME,A.SBK009 INV_PAYDAYS,datediff(day,dateadd(day,A.SBK009,A.SBK003),getdate()) INV_DAYS,dateadd(day,A.SBK009,A.SBK003) INV_PAYEND,'65' INV_SIGN,A.SBK023 INV_ACCSIGN,
INV_ACCSIGNNAME=CASE A.SBK023
WHEN '0' THEN '未记账'
WHEN '1' THEN '已记账' ELSE '无需记账' END
from SELSBK A
INNER JOIN CMSINV H ON A.SBK001=H.INV002
INNER JOIN CUSCBA D ON A.SBK004=D.CBA000
INNER JOIN CMSEPA C ON A.SBK012=C.EPA001
LEFT JOIN CMSDPA F ON A.SBK011=F.DPA001
LEFT JOIN JXCPWZ E ON A.SBK008=E.PWZ001
WHERE A.SBK020='3' and A.SBK026<>'0' and ((A.SBK014-A.SBK018)>0)
union all
--销售单(审核+余额大于0)
select substring(a.SAL001,1,2) INV_Class,A.SAL001 INV_ID,H.INV003 INV_NAME,A.SAL002 INV_NO,A.SAL003 INV_DATE,A.SAL004 INV_OBJID,
D.CBA001 INV_OBJNO,D.CBA002 INV_OBJNAME,A.SAL005 INV_RELID,A.SAL019 INV_OPDEPTNO,F.DPA002 INV_OPDEPTNAME,A.SAL020 INV_OPUSERID,
C.EPA002 INV_OPUSERNO,C.EPA003 INV_OPUSERNAME,(A.SAL024+A.SAL025) INV_SUM,(A.SAL024+A.SAL025-A.SAL029) INV_LASTSUM,
(A.SAL024+A.SAL025-A.SAL026-A.SAL027-A.SAL028) INV_GAIN,A.SAL016 INV_TICHEN,A.SAL043 INV_REMARK,A.SAL013 INV_PAYNO,
E.PWZ002 INV_PAYNAME,A.SAL014 INV_PAYDAYS,datediff(day,dateadd(day,A.SAL014,A.SAL003),getdate()) INV_DAYS,dateadd(day,A.SAL014,A.SAL003) INV_PAYEND,'66' INV_SIGN,A.SAL034 INV_ACCSIGN,
INV_ACCSIGNNAME=CASE A.SAL034
WHEN '0' THEN '未记账'
WHEN '1' THEN '已记账' ELSE '无需记账' END
from SELSAL A
INNER JOIN CMSINV H ON A.SAL001=H.INV002
INNER JOIN CUSCBA D ON A.SAL004=D.CBA000
INNER JOIN CMSEPA C ON A.SAL020=C.EPA001
LEFT JOIN CMSDPA F ON A.SAL019=F.DPA001
LEFT JOIN JXCPWZ E ON A.SAL013=E.PWZ001
WHERE A.SAL031='3' and ((A.SAL024+A.SAL025-A.SAL029)>0)
union all
--采购退货单(审核+余额大于0)
select substring(a.PBK001,1,2) INV_Class,A.PBK001 INV_ID,H.INV003 INV_NAME,A.PBK002 INV_NO,A.PBK003 INV_DATE,A.PBK004 INV_OBJID,
D.CBA001 INV_OBJNO,D.CBA002 INV_OBJNAME,A.PBK005 INV_RELID,A.PBK013 INV_OPDEPTNO,F.DPA002 INV_OPDEPTNAME,A.PBK014 INV_OPUSERID,
C.EPA002 INV_OPUSERNO,C.EPA003 INV_OPUSERNAME,A.PBK016 INV_SUM,(A.PBK016-A.PBK019) INV_LASTSUM,
0 INV_GAIN,0 INV_TICHEN,A.PBK033 INV_REMARK,A.PBK010 INV_PAYNO,E.PWZ002 INV_PAYNAME,A.PBK011 INV_PAYDAYS,
datediff(day,dateadd(day,A.PBK011,A.PBK003),getdate()) INV_DAYS,dateadd(day,A.PBK011,A.PBK003) INV_PAYEND,'66' INV_SIGN,A.PBK024 INV_ACCSIGN,INV_ACCSIGNNAME=CASE A.PBK024
WHEN '0' THEN '未记账'
WHEN '1' THEN '已记账' ELSE '无需记账' END
from PURPBK A
INNER JOIN CMSINV H ON A.PBK001=H.INV002
INNER JOIN CUSCBA D ON A.PBK004=D.CBA000
INNER JOIN CMSEPA C ON A.PBK014=C.EPA001
LEFT JOIN CMSDPA F ON A.PBK013=F.DPA001
LEFT JOIN JXCPWZ E ON A.PBK008=E.PWZ001
WHERE A.PBK021='3' and ((A.PBK016-A.PBK019)>0)
union all
--服务单(完工、审核+余额大于0)
select substring(a.REP001,1,2) INV_Class,A.REP001 INV_ID,H.INV003 INV_NAME,A.REP002 INV_NO,A.REP043 INV_DATE,A.REP009 INV_OBJID,
D.CBA001 INV_OBJNO,D.CBA002 INV_OBJNAME,A.REP010 INV_RELID,A.REP037 INV_OPDEPTNO,F.DPA002 INV_OPDEPTNAME,A.REP038 INV_OPUSERID,
C.EPA002 INV_OPUSERNO,C.EPA003 INV_OPUSERNAME,(A.REP067+A.REP068) INV_SUM,(A.REP067+A.REP068-A.REP071) INV_LASTSUM,
(A.REP067+A.REP068-A.REP069-A.REP070-A.REP073-A.REP074-A.REP075) INV_GAIN,(A.REP025+A.REP050) INV_TICHEN,A.REP030 INV_REMARK,
A.REP079 INV_PAYNO,E.PWZ002 INV_PAYNAME,A.REP080 INV_PAYDAYS,datediff(day,dateadd(day,A.REP080,A.REP043),getdate()) INV_DAYS,
dateadd(day,A.REP080,A.REP043) INV_PAYEND,'66' INV_SIGN,A.REP076 INV_ACCSIGN,
INV_ACCSIGNNAME=CASE A.REP076
WHEN '0' THEN '未记账'
WHEN '1' THEN '已记账' ELSE '无需记账' END
from SERREP A
INNER JOIN CMSINV H ON A.REP001=H.INV002
INNER JOIN CUSCBA D ON A.REP009=D.CBA000
INNER JOIN CMSEPA C ON A.REP038=C.EPA001
LEFT JOIN CMSDPA F ON A.REP037=F.DPA001
LEFT JOIN JXCPWZ E ON A.REP079=E.PWZ001
WHERE (A.REP048 in ('2','3','4')) and ((A.REP067+A.REP068-A.REP071)>0)
union all
--维修单收款
select substring(a.RPO001,1,2) INV_Class,A.RPO001 INV_ID,H.INV003 INV_NAME,A.RPO002 INV_NO,A.RPO040 INV_DATE,A.RPO011 INV_OBJID,
D.CBA001 INV_OBJNO,D.CBA002 INV_OBJNAME,A.RPO012 INV_RELID,A.RPO032 INV_OPDEPTNO,F.DPA002 INV_OPDEPTNAME,A.RPO033 INV_OPUSERID,
C.EPA002 INV_OPUSERNO,C.EPA003 INV_OPUSERNAME,(A.RPO048+A.RPO049) INV_SUM,(A.RPO048+A.RPO049-A.RPO081) INV_LASTSUM,
(A.RPO048+A.RPO049-A.RPO050-A.RPO051-A.RPO052-A.RPO053-A.RPO054) INV_GAIN,(A.RPO036+A.RPO055) INV_TICHEN,A.RPO072 INV_REMARK,
A.RPO078 INV_PAYNO,E.PWZ002 INV_PAYNAME,A.RPO079 INV_PAYDAYS,datediff(day,dateadd(day,A.RPO079,A.RPO040),getdate()) INV_DAYS,
dateadd(day,A.RPO079,A.RPO040) INV_PAYEND,'66' INV_SIGN,A.RPO080 INV_ACCSIGN,
INV_ACCSIGNNAME=CASE A.RPO080
WHEN '0' THEN '未记账'
WHEN '1' THEN '已记账' ELSE '无需记账' END
from SERRPO A
INNER JOIN CMSINV H ON A.RPO001=H.INV002
INNER JOIN CUSCBA D ON A.RPO011=D.CBA000
INNER JOIN CMSEPA C ON A.RPO033=C.EPA001
LEFT JOIN CMSDPA F ON A.RPO032=F.DPA001
LEFT JOIN JXCPWZ E ON A.RPO078=E.PWZ001
WHERE (A.RPO037 in ('4','5','6')) and ((A.RPO048+A.RPO049-A.RPO081)>0) and A.RPO003='32'