对于想这种频繁的视图如何看待

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

由于要做某个模块,想拿来先前同事写的视图代码一用,可是那来后我才发觉是这样子的

create view zc_ypz as

select zcgs,dwdm,zysx,hj,1 as kpml,sum(yzje) as aa,0 as bb,0 as cc,0 as dd from zc_skpk

where zcgs=1 and zysx<>10 and zysx<>7 and (mlbh like '100%' or mlbh like '110%') and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs,dwdm,zysx,hj,2 as kpml,sum(yzje) as aa,0 as bb,0 as cc,0 as dd from zc_skpk

where zcgs=1 and zysx<>10 and zysx<>7 and (mlbh like '010%' or mlbh like '030%') and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,3 as kpml,sum(yzje) as aa,0 as bb,0 as cc,0 as dd from zc_skpk

where zcgs=1 and zysx<>10 and zysx<>7 and mlbh like '040%' and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,4 as kpml,sum(yzje) as aa,0 as bb,0 as cc,0 as dd from zc_skpk

where zcgs=1 and zysx<>10 and zysx<>7 and mlbh like '05001%' and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,5 as kpml,sum(yzje) as aa,0 as bb,0 as cc,0 as dd from zc_skpk

where zcgs=1 and zysx<>10 and zysx<>7 and (mlbh not like '100%' and mlbh not like '110%' and mlbh

not like '010%' and mlbh not like '030%' and mlbh not like '040%' and mlbh not like '05001%') and

mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,1 as kpml,0 as aa,sum(yzje) as bb,0 as cc,0 as dd from zc_skpk

where zcgs=2 and zysx<>10 and zysx<>7 and (mlbh like '100%' or mlbh like '110%') and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,2 as kpml,0 as aa,sum(yzje) as bb,0 as cc,0 as dd from zc_skpk

where zcgs=2 and zysx<>10 and zysx<>7 and (mlbh like '010%' or mlbh like '030%') and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,3 as kpml,0 as aa,sum(yzje) as bb,0 as cc,0 as dd from zc_skpk

where zcgs=2 and zysx<>10 and zysx<>7 and mlbh like '040%' and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,4 as kpml,0 as aa,sum(yzje) as bb,0 as cc,0 as dd from zc_skpk

where zcgs=2 and zysx<>10 and zysx<>7 and mlbh like '05001%' and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,5 as kpml,0 as aa,sum(yzje) as bb,0 as cc,0 as dd from zc_skpk

where zcgs=2 and zysx<>10 and zysx<>7 and (mlbh not like '100%' and mlbh not like '110%' and mlbh

not like '010%' and mlbh not like '030%' and mlbh not like '040%' and mlbh not like '05001%') and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,1 as kpml,0 as aa,0 as bb,sum(yzje) as cc,0 as dd from zc_skpk

where zcgs=3 and zysx<>10 and zysx<>7 and (mlbh like '100%' or mlbh like '110%') and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,2 as kpml,0 as aa,0 as bb,sum(yzje) as cc,0 as dd from zc_skpk

where zcgs=3 and zysx<>10 and zysx<>7 and (mlbh like '010%' or mlbh like '030%') and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,3 as kpml,0 as aa,0 as bb,sum(yzje) as cc,0 as dd from zc_skpk

where zcgs=3 and zysx<>10 and zysx<>7 and mlbh like '040%' and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,4 as kpml,0 as aa,0 as bb,sum(yzje) as cc,0 as dd from zc_skpk

where zcgs=3 and zysx<>10 and zysx<>7 and mlbh like '05001%' and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,5 as kpml,0 as aa,0 as bb,sum(yzje) as cc,0 as dd from zc_skpk

where zcgs=3 and zysx<>10 and zysx<>7 and (mlbh not like '100%' and mlbh not like '110%' and mlbh

not like '010%' and mlbh not like '030%' and mlbh not like '040%' and mlbh not like '05001%') and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,1 as kpml,0 as aa,0 as bb,0 as cc,sum(yzje) as dd from zc_skpk

where zcgs=4 and zysx<>10 and zysx<>7 and (mlbh like '100%' or mlbh like '110%') and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,2 as kpml,0 as aa,0 as bb,0 as cc,sum(yzje) as dd from zc_skpk

where zcgs=4 and zysx<>10 and zysx<>7 and (mlbh like '010%' or mlbh like '030%') and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,3 as kpml,0 as aa,0 as bb,0 as cc,sum(yzje) as dd from zc_skpk

where zcgs=4 and zysx<>10 and zysx<>7 and mlbh like '040%' and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,4 as kpml,0 as aa,0 as bb,0 as cc,sum(yzje) as dd from zc_skpk

where zcgs=4 and zysx<>10 and zysx<>7 and mlbh like '05001%' and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,5 as kpml,0 as aa,0 as bb,0 as cc,sum(yzje) as dd from zc_skpk

where zcgs=4 and zysx<>10 and zysx<>7 and (mlbh not like '100%' and mlbh not like '110%' and mlbh

not like '010%' and mlbh not like '030%' and mlbh not like '040%' and mlbh not like '05001%') and mlbh not like '120%' group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,0 as kpml,sum(yzje) as aa,0 as bb,0 as cc,0 as dd from zc_skpk where

zcgs=1 and zysx=10 group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,0 as kpml,0 as aa,sum(yzje) as bb,0 as cc,0 as dd from zc_skpk where

zcgs=2 and zysx=10 group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,0 as kpml,0 as aa,0 as bb,sum(yzje) as cc,0 as dd from zc_skpk where

zcgs=3 and zysx=10 group by dwdm,zysx,hj,zcgs

union

select zcgs, dwdm,zysx,hj,0 as kpml,0 as aa,0 as bb,0 as cc,sum(yzje) as dd from zc_skpk where

zcgs=4 and zysx=10 group by dwdm,zysx,hj,zcgs

union

select b.zcgs, b.dwdm,a.zylxbh as zysx,b.hj,1 as kpml,sum(a.ZYLX_JE) as aa,0 as bb,0 as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=1 and a.zylxbh<>10 and (b.mlbh like '100%' or b.mlbh like '110%') and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,2 as kpml,sum(a.ZYLX_JE) as aa,0 as bb,0 as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=1 and a.zylxbh<>10 and (b.mlbh like '010%' or b.mlbh like '030%') and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,3 as kpml,sum(a.ZYLX_JE) as aa,0 as bb,0 as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=1 and a.zylxbh<>10 and b.mlbh like '040%' and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,4 as kpml,sum(a.ZYLX_JE) as aa,0 as bb,0 as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=1 and a.zylxbh<>10 and b.mlbh like '05001%' and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,5 as kpml,sum(a.ZYLX_JE) as aa,0 as bb,0 as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=1 and a.zylxbh<>10 and (b.mlbh not like '100%' and b.mlbh not like '110%' and b.mlbh not

like '010%' and b.mlbh not like '030%' and b.mlbh not like '040%' and b.mlbh not like '05001%')

and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,1 as kpml,0 as aa,sum(a.ZYLX_JE) as bb,0 as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=2 and a.zylxbh<>10 and (b.mlbh like '100%' or b.mlbh like '110%') and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,2 as kpml,0 as aa,sum(a.ZYLX_JE) as bb,0 as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=2 and a.zylxbh<>10 and (b.mlbh like '010%' or b.mlbh like '030%') and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,3 as kpml,0 as aa,sum(a.ZYLX_JE) as bb,0 as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=2 and a.zylxbh<>10 and b.mlbh like '040%' and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,4 as kpml,0 as aa,sum(a.ZYLX_JE) as bb,0 as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=2 and a.zylxbh<>10 and b.mlbh like '05001%' and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,5 as kpml,0 as aa,sum(a.ZYLX_JE) as bb,0 as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=2 and a.zylxbh<>10 and (b.mlbh not like '100%' and b.mlbh not like '110%' and b.mlbh not

like '010%' and b.mlbh not like '030%' and b.mlbh not like '040%' and b.mlbh not like '05001%')

and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,1 as kpml,0 as aa,0 as bb,sum(a.ZYLX_JE) as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=3 and a.zylxbh<>10 and (b.mlbh like '100%' or b.mlbh like '110%') and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,2 as kpml,0 as aa,0 as bb,sum(a.ZYLX_JE) as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=3 and a.zylxbh<>10 and (b.mlbh like '010%' or b.mlbh like '030%') and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,3 as kpml,0 as aa,0 as bb,sum(a.ZYLX_JE) as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=3 and a.zylxbh<>10 and b.mlbh like '040%' and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,4 as kpml,0 as aa,0 as bb,sum(a.ZYLX_JE) as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=3 and a.zylxbh<>10 and b.mlbh like '05001%' and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,5 as kpml,0 as aa,0 as bb,sum(a.ZYLX_JE) as

cc,0 as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and

b.zcgs=3 and a.zylxbh<>10 and (b.mlbh not like '100%' and b.mlbh not like '110%' and b.mlbh not

like '010%' and b.mlbh not like '030%' and b.mlbh not like '040%' and b.mlbh not like '05001%')

and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,1 as kpml,0 as aa,

0 as bb,0 as cc,sum(a.ZYLX_JE) as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and

a.dwbh=b.dwdm and b.zysx=7 and b.zcgs=4 and a.zylxbh<>10 and (b.mlbh like '100%' or b.mlbh like

'110%') and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,2 as kpml,0 as aa,0 as bb,0 as cc,sum

(a.ZYLX_JE) as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7

and b.zcgs=4 and a.zylxbh<>10 and (b.mlbh like '010%' or b.mlbh like '030%') and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,3 as kpml,0 as aa,0 as bb,0 as cc,sum

(a.ZYLX_JE) as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7

and b.zcgs=4 and a.zylxbh<>10 and b.mlbh like '040%' and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,4 as kpml,0 as aa,0 as bb,0 as cc,sum

(a.ZYLX_JE) as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7

and b.zcgs=4 and a.zylxbh<>10 and b.mlbh like '05001%' and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,5 as kpml,0 as aa,0 as bb,0 as cc,sum

(a.ZYLX_JE) as dd from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7

and b.zcgs=4 and a.zylxbh<>10 and (b.mlbh not like '100%' and b.mlbh not like '110%' and b.mlbh not like '010%' and b.mlbh not like '030%' and b.mlbh not like '040%' and b.mlbh not like '05001%') and mlbh not like '120%' group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,0 as kpml,sum(a.ZYLX_JE) as aa,0 as bb,0 as cc,0 as dd

from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and b.zcgs=1 and

a.zylxbh=10 group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,0 as kpml,0 as aa,sum(a.ZYLX_JE) as bb,0 as cc,0 as dd

from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and b.zcgs=2 and

a.zylxbh=10 group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,0 as kpml,0 as aa,0 as bb,sum(a.ZYLX_JE) as cc,0 as dd

from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and b.zcgs=3 and

a.zylxbh=10 group by b.dwdm,a.zylxbh,b.hj,b.zcgs

union

select b.zcgs,b.dwdm,a.zylxbh as zysx,b.hj,0 as kpml,0 as aa,0 as bb,0 as cc,sum(a.ZYLX_JE) as dd

from zc_kpzylx a,zc_skpk b where a.kpbh=b.kpbh and a.dwbh=b.dwdm and b.zysx=7 and b.zcgs=4 and

a.zylxbh=10 group by b.dwdm,a.zylxbh,b.hj,b.zcgs;

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航