到底是用视图好还是不使用视图了?
比如我程序中用到很多这样的组合的查询:
select A.COMPANY,A.CREATE_DATE,A.CREATOR,C.EPA002 CREATOR_NO,C.EPA003 CREATOR_NAME,
A.MODI_DATE,A.MODIFIER,C1.EPA002 MODIFIER_NO,C1.EPA003 MODIFIER_NAME,
A.FLAG,A.CBA000,A.CBA001,A.CBA002,A.CBA003,CBA003_NAME=case a.CBA003
when '0' then '单位客户'
when '1' then '个人客户'
when '2' then '供销商'
when '3' then '供应商'
end,A.CBA004,b.CGP003 CBA004_NAME,a.CBA005,a.CBA006,F.CST002 CBA006_Name,F.CST005 CBA006_Class,a.CBA007,a.CBA008,a.CBA009,a.CBA010,A1.CBA002 CBA010_Name,a.CBA011,a.CBA012,a.CBA013,a.CBA014,a.CBA015,a.CBA016,a.CBA017,a.CBA018,a.CBA019,a.CBA020,a.CBA021,d.CDA002,d.CDA004,d.CDA009,d.CDA016,d.CDA017,d.CDA021,d.CDA022,d.CDA025,d.CDA026,d.CDA027,d.CDA033,d.CDA048,d.CDA049,d.CDA050,d.CDA057,a.CBA022,a.CBA023,a.CBA024,a.CBA025,a.CBA026,a.CBA027,a.CBA028,a.CBA029,a.CBA030,a.CBA031,a.CBA032,a.CBA033,a.CBA034,a.CBA035,a.CBA036,a.CBA037,a.CBA038,a.CBA039,A.CBA040,A.CBA041,F1.PWZ002 CBA041_Name,F1.PWZ003 CBA041_Days,A.CBA042,A.CBA043,A.CBA044,A.CBA045,A.CBA046,A.CBA047,A.CBA048,A.CBA049,A.CBA050,A.CBA051,A.CBA052,A.CBA053,A.CBA054,A.CBA055,A.CBA056,c2.EPA002 CBA056_NO,c2.EPA003 CBA056_NAME,A.CBA057,A.CBA058,A.CBA060,c3.EPA002 CBA060_NO,c3.EPA003 CBA060_NAME,A.CBA061,A.CBA062,A.CBA063,A.CBA064,A.CBA065,A.CBA066,A.CBA067,A.CBA068,(A.CBA067-A.CBA068+A.CBA066-A.CBA065) Debt,A.CBA069,A.CBA070,A.CBA073,A.CBA074,A.CBA075,A.CBA076,A.CBA077,A.CBA078,A.CBA079,A.CBA080,
A.CBA081,A.CBA082,A.CBA083,E.TPA002 CBA083_NAME,E1.TPB003 CBA083_DAYS,A.CBA084,A.CBA085,A.CBA086,A.CBA087,A.CBA088,A.CBA089,A.CBA090,A.CBA091
from CUSCBA a
left join CMSCGP b on a.CBA004=b.CGP001 and b.CGP002='3'
left join CMSEPA c on a.CREATOR=c.EPA001
left join CMSEPA c1 on a.MODIFIER=c1.EPA001
left join CMSEPA c2 on a.CBA056=c2.EPA001
left join CMSEPA c3 on a.CBA060=c3.EPA001
left join CUSCST F on a.CBA006=F.CST001
left join JXCPWZ F1 on a.CBA041=F1.PWZ001
left join CMSTPA E on a.CBA083=E.TPA001
left join CMSTPB E1 on a.CBA083=E1.TPB001 and A.CBA084=(E1.TPB002-1)
left join CUSCDA d on a.CBA021=d.CDA001
left join CUSCBA A1 on a.CBA010=A1.CBA001
还有很多业务单据也是这样组合的,
不用视图嘛,听说查询速度效率高些,设置查询条件稍微灵活些,怕以后数据多了影响效率;
但问题也很多:比如别名太多,容易搞混,还有由于系统有权限过滤和报表打印,我都需要把这些组合语句存储到数据库中,遇到有显示字段变动时很麻烦。
现在头疼的是到底要不要把这类语句全写成视图了?高手帮我分析一下利弊。谢谢!