在使用视图的过程中碰到了很多问题，其实试图并不能真正提高效率，只是更加方便的给用户提供了操作，使用户有了更加条理的思路，使用视图时能用连接就用连接，而子查询效率明显差很远。
以下是我的对比:
CREATE VIEW DCLSVIEW AS SELECT ksjbxx.zkzh, ksjbxx.xm, ksjbxx.sfzh, ksjbxx.xb, ksjbxx.csny,ksjbxx.szxx, isnull((SELECT sfbhb.mc FROM sfbhb WHERE sfbhb.bh = ksjbxx.szsf), '') AS sfmc,isnull((SELECT mc FROM kdxx WHERE kdxx.bh = ksjbxx.ksdd), '') AS ksdd, ksjbxx.ksh,isnull((SELECT kslbbhb.mc FROM kslbbhb WHERE kslbbhb.bh = ksjbxx.kslb), '') AS kslb,isnull((SELECT zyfxbhb.mc FROM zyfxbhb WHERE zyfxbhb.bh = ksjbxx.zyfx), '') AS zyfx,ksjbxx.txdz,ksjbxx.cf AS cf , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =001 ),'0') as zyf001 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =002 ),'0') as zyf002 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =003 ),'0') as zyf003 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =004 ),'0') as zyf004 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =005 ),'0') as zyf005 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =006 ),'0') as zyf006 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =007 ),'0') as zyf007 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =008 ),'0') as zyf008 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =009 ),'0') as zyf009 , isnull((select jf from zyfs where zkzh = ksjbxx.zkzh and kmbh =010 ),'0') as zyf010 , isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =01 ),'0') as whf01, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =02 ),'0') as whf02, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =03 ),'0') as whf03, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =04 ),'0') as whf04, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =05 ),'0') as whf05, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =06 ),'0') as whf06, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =07 ),'0') as whf07, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =08 ),'0') as whf08, isnull((select hdf from whfslrb where whfslrb.zkzh = ksjbxx.zkzh and whfslrb.whkm =09 ),'0') as whf09 , isnull((select jzzf from zyfzfb where zkzh =ksjbxx.zkzh),'0') as zyfjzzf,isnull((select place from zyfzfb where zkzh =ksjbxx.zkzh),'0') as place ,isnull((select sfplace from zyfzfb where zkzh =ksjbxx.zkzh),'0') as sfplace ,isnull((select zyzysx from syb where syb.zkzh = ksjbxx.zkzh and syb.zyzysx = 1),'') as zysx1 ,isnull(( select mc from whklbbhb where whklbbhb.bh in (select whkslb from whfs where whfs.zkzh = ksjbxx.zkzh)),'') as whkslb,isnull((select whfzf from whfs where whfs.zkzh = ksjbxx.zkzh ),'') as whfzf ,isnull((select whfzfdl from whfs where whfs.zkzh = ksjbxx.zkzh),'') as whfzfdl , isnull((select sfgsk from whfs where whfs.zkzh = ksjbxx.zkzh),'') as sfgsk ,isnull((select case lqzt when '1' then '是' when '0' then '否' else null end from ylqxx where ylqxx.zkzh = ksjbxx.zkzh ),'') as ylqzt ,isnull((select case sfzzlq when '1' then '是' when '0' then '否' else null end from zzlqxx where zzlqxx.zkzh = ksjbxx.zkzh ),'') as zzlqzt from ksjbxx
评论加载中…
 |