在使用视图的过程中碰到了很多问题，其实试图并不能真正提高效率，只是更加方便的给用户提供了操作，使用户有了更加条理的思路，使用视图时能用连接就用连接，而子查询效率明显差很远。

以下是我的对比:

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

以上试图的查询可能会用到三分多钟，

如果用下面师徒的创建过程，可能你只需要十秒不到的时间

SELECT dbo.ksjbxx.zkzh, dbo.ksjbxx.xm, dbo.ksjbxx.sfzh, dbo.ksjbxx.xb, dbo.ksjbxx.csny,
      dbo.ksjbxx.szxx, dbo.sfbhb.mc AS sfmc, dbo.kdxx.mc AS ksdd, dbo.ksjbxx.ksh,
      dbo.kslbbhb.mc AS kslb, dbo.zyfxbhb.mc AS zyfx, dbo.ksjbxx.txdz, dbo.ksjbxx.cf,
      dbo.zyfzfb.zyfzf, dbo.zyfzfb.jzzf AS zyfjzzf, dbo.zyfzfb.place, dbo.zyfzfb.sfplace,
      dbo.whklbbhb.mc

sql server 2005中的except和INTERSECT运算 sql server的保留关键字

评论 (0) All