由于要做某个模块，想拿来先前同事写的视图代码一用，可是那来后我才发觉是这样子的
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;
