62,046
社区成员
发帖
与我相关
我的任务
分享
--百分比视图
drop view v_MB_Prog_v3;
create view v_MB_Prog_v3
(
c_proj_id,
c_proj_year,
c_zonename,
c_proj_c33,
c_year,
c_proj_fsum,
c_proj_f1,
c_proj_f2,
c_proj_f3_4,
c_proj_f5,
c_proj_f6,
c_proj_f7,
c_proj_f11,
c_proj_c39,
c_proj_c18,
c_proj_c11,
c_proj_c12,
c_proj_c40,
c_proj_c24_25,
c_proj_c1_2,
c_proj_c9_10,
c_proj_c3_4,
c_proj_c41,
c_proj_c16,
c_proj_c17,
c_proj_c42,
c_proj_c27,
c_proj_c28,
c_proj_c29,
c_proj_c30,
c_proj_c43,
c_proj_c44,
c_proj_c31,
c_proj_c32,
c_proj_c45,
c_proj_c26,
c_proj_c20,
c_proj_c21,
c_proj_c23,
c_proj_c46,
bz,s1,s2,
bdt,
edt,
yssj,
c_proj_c48,
c_proj_c49,
c_proj_c50,
cysj,
c_proj_c51,
c_proj_c52,
c_proj_b4,
c_proj_b5,
c_proj_b8,
c_proj_b9,
c_proj_b10
)
as
select
v_MB_Prog_v2.c_proj_id,
v_MB_Prog_v2.c_proj_year,
v_MB_Prog_v2.c_zonename,
v_MB_Prog_v2.c_proj_c33,
v_MB_Prog_v2.c_year,
isnull(round(v_MB_Prog_v2.c_proj_fsum/nullif(v_MB_Prog_v1.c_proj_fsum*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_f1/nullif(v_MB_Prog_v1.c_proj_f1*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_f2/nullif(v_MB_Prog_v1.c_proj_f2*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_f3_4/nullif(v_MB_Prog_v1.c_proj_f3_4*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_f5/nullif(v_MB_Prog_v1.c_proj_f5*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_f6/nullif(v_MB_Prog_v1.c_proj_f6*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_f7/nullif(v_MB_Prog_v1.c_proj_f7*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_f11/nullif(v_MB_Prog_v1.c_proj_f11*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c39/nullif(v_MB_Prog_v1.c_proj_c39*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c18/nullif(v_MB_Prog_v1.c_proj_c18*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c11/nullif(v_MB_Prog_v1.c_proj_c11*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c12/nullif(v_MB_Prog_v1.c_proj_c12*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c40/nullif(v_MB_Prog_v1.c_proj_c40*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c24_25/nullif(v_MB_Prog_v1.c_proj_c24_25*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c1_2/nullif(v_MB_Prog_v1.c_proj_c1_2*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c9_10/nullif(v_MB_Prog_v1.c_proj_c9_10*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c3_4/nullif(v_MB_Prog_v1.c_proj_c3_4*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c41/nullif(v_MB_Prog_v1.c_proj_c41*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c16/nullif(v_MB_Prog_v1.c_proj_c16*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c17/nullif(v_MB_Prog_v1.c_proj_c17*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c42/nullif(v_MB_Prog_v1.c_proj_c42*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c27/nullif(v_MB_Prog_v1.c_proj_c27*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c28/nullif(v_MB_Prog_v1.c_proj_c28*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c29/nullif(v_MB_Prog_v1.c_proj_c29*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c30/nullif(v_MB_Prog_v1.c_proj_c30*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c43/nullif(v_MB_Prog_v1.c_proj_c43*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c44/nullif(v_MB_Prog_v1.c_proj_c44*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c31/nullif(v_MB_Prog_v1.c_proj_c31*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c32/nullif(v_MB_Prog_v1.c_proj_c32*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c45/nullif(v_MB_Prog_v1.c_proj_c45*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c26/nullif(v_MB_Prog_v1.c_proj_c26*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c20/nullif(v_MB_Prog_v1.c_proj_c20*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c21/nullif(v_MB_Prog_v1.c_proj_c21*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c23/nullif(v_MB_Prog_v1.c_proj_c23*100,0),0),0),
isnull(round(v_MB_Prog_v2.c_proj_c46/nullif(v_MB_Prog_v1.c_proj_c46*100,0),0),0),
'4',
(select proj_type from mb_proj where mb_proj.proj_id = v_MB_Prog_v1.c_proj_id) ,
(select proj_pc from mb_proj where mb_proj.proj_id = v_MB_Prog_v1.c_proj_id) ,
v_MB_Prog_v2.bdt,
v_MB_Prog_v2.edt,
v_MB_Prog_v2.yssj,
v_MB_Prog_v2.c_proj_c48,
v_MB_Prog_v2.c_proj_c49,
v_MB_Prog_v2.c_proj_c50,
v_MB_Prog_v2.cysj,
v_MB_Prog_v2.c_proj_c51,
v_MB_Prog_v2.c_proj_c52,
v_MB_Prog_v2.c_proj_b4,
v_MB_Prog_v2.c_proj_b5,
v_MB_Prog_v2.c_proj_b8,
v_MB_Prog_v2.c_proj_b9,
v_MB_Prog_v2.c_proj_b10
from v_MB_Prog_v1,v_MB_Prog_v2
where v_MB_Prog_v1.c_proj_id = v_MB_Prog_v2.c_proj_id and v_MB_Prog_v1.c_year=v_MB_Prog_v2.c_year and v_MB_Prog_v1.bz=2
select * from v_mb_prog_v3;
--视图四
drop view v_mb_prog_v4;
create view v_MB_Prog_v4
(
c_proj_id,
c_proj_year,
c_zonename,
c_proj_c33,
c_year,
c_proj_fsum,
c_proj_f1,
c_proj_f2,
c_proj_f3_4,
c_proj_f5,
c_proj_f6,
c_proj_f7,
c_proj_f11,
c_proj_c39,
c_proj_c18,
c_proj_c11,
c_proj_c12,
c_proj_c40,
c_proj_c24_25,
c_proj_c1_2,
c_proj_c9_10,
c_proj_c3_4,
c_proj_c41,
c_proj_c16,
c_proj_c17,
c_proj_c42,
c_proj_c27,
c_proj_c28,
c_proj_c29,
c_proj_c30,
c_proj_c43,
c_proj_c44,
c_proj_c31,
c_proj_c32,
c_proj_c45,
c_proj_c26,
c_proj_c20,
c_proj_c21,
c_proj_c23,
c_proj_c46,
bz,s1,s2,
bdt,
edt,
yssj,
c_proj_c48,
c_proj_c49,
c_proj_c50,
cysj,
c_proj_c51,
c_proj_c52,
c_proj_b4,
c_proj_b5,
c_proj_b8,
c_proj_b9,
c_proj_b10
)
as
select * from v_mb_prog_v1
union all
select * from v_MB_Prog_v2
union all
select * from v_MB_Prog_v3
select * from v_mb_prog_v4;
--视图一 获取固有信息里的统计字段
drop view v_MB_Prog_v1;
create view v_MB_Prog_v1
(
c_proj_id,
c_proj_year,
c_zonename,
c_proj_c33,
c_year,
c_proj_fsum,
c_proj_f1,
c_proj_f2,
c_proj_f3_4,
c_proj_f5,
c_proj_f6,
c_proj_f7,
c_proj_f11,
c_proj_c39,
c_proj_c18,
c_proj_c11,
c_proj_c12,
c_proj_c40,
c_proj_c24_25,
c_proj_c1_2,
c_proj_c9_10,
c_proj_c3_4,
c_proj_c41,
c_proj_c16,
c_proj_c17,
c_proj_c42,
c_proj_c27,
c_proj_c28,
c_proj_c29,
c_proj_c30,
c_proj_c43,
c_proj_c44,
c_proj_c31,
c_proj_c32,
c_proj_c45,
c_proj_c26,
c_proj_c20,
c_proj_c21,
c_proj_c23,
c_proj_c46,
bz,
s1,
s2,
bdt,
edt,
yssj,
c_proj_c48,
c_proj_c49,
c_proj_c50,
cysj,
c_proj_c51,
c_proj_c52,
c_proj_b4,
c_proj_b5,
c_proj_b8,
c_proj_b9,
c_proj_b10
)
as
select
c_proj_id,
c_proj_year,
c_zonename,
c_proj_c33,
case c_proj_typeinfo
when '1' then CAST ( c_proj_year AS int ) + 2
when '2' then CAST ( c_proj_year AS int )
when '3' then CAST ( c_proj_year AS int )
when '4' then CAST ( c_proj_year AS int ) + 1
when '5' then CAST ( c_proj_year AS int ) + 1
when '6' then CAST ( c_proj_year AS int ) + 2
when '7' then CAST ( c_proj_year AS int ) + 2
end ,
isnull(c_proj_f1+c_proj_f2+c_proj_f3+c_proj_f4+c_proj_f5+c_proj_f6+c_proj_f7,0) as c_proj_fsum,
isnull(c_proj_f1,0) as c_proj_f1,
isnull(c_proj_f2,0) as c_proj_f2,
isnull(c_proj_f3+c_proj_f4,0) as c_proj_f3_4,
isnull(c_proj_f5,0) as c_proj_f5,
isnull(c_proj_f6,0) as c_proj_f6,
isnull(c_proj_f7,0) as c_proj_f7,
isnull(c_proj_f11,0) as c_proj_f11,
isnull(c_proj_c39,0) as c_proj_c39 ,
isnull(c_proj_c18,0) as c_proj_c18,
isnull(c_proj_c11,0) as c_proj_c11,
isnull(c_proj_c12,0) as c_proj_c12,
isnull(c_proj_c40,0) as c_proj_c40,
isnull(c_proj_c24+c_proj_c25,0) as c_proj_c24_25,
isnull(c_proj_c1+c_proj_c2,0) as c_proj_c1_2,
isnull(c_proj_c9+c_proj_c10,0) as c_proj_cf9_10,
isnull(c_proj_c3+c_proj_c4,0) as c_proj_c3_4,
isnull(c_proj_c41,0) as c_proj_c41,
isnull(c_proj_c16,0) as c_proj_c16,
isnull(c_proj_c17,0) as c_proj_c17,
isnull(c_proj_c42,0) as c_proj_c42,
isnull(c_proj_c27,0) as c_proj_c27,
isnull(c_proj_c28,0) as c_proj_c28,
isnull(c_proj_c29,0) as c_proj_c29,
isnull(c_proj_c30,0) as c_proj_c30,
isnull(c_proj_c43,0) as c_proj_c43,
isnull(c_proj_c44,0) as c_proj_c44,
isnull(c_proj_c31,0) as c_proj_c31,
isnull(c_proj_c32,0) as c_proj_c32,
isnull(c_proj_c45,0) as c_proj_c45,
isnull(c_proj_c26,0) as c_proj_c26,
isnull(c_proj_c20,0) as c_proj_c20,
isnull(c_proj_c21,0) as c_proj_c21,
isnull(c_proj_c23,0) as c_proj_c23,
isnull(c_proj_c46,0) as c_proj_c46,
(case c_proj_typeinfo
when '2' then 1
when '3' then 2
when '4' then 1
when '5' then 2
when '6' then 1
when '7' then 2
else 0
end),
(select proj_type from mb_proj where mb_proj.proj_id = MB_Prog_GYXX.c_proj_id) ,
(select proj_pc from mb_proj where mb_proj.proj_id = MB_Prog_GYXX.c_proj_id),
(select c_bdt from mb_proj where mb_proj.proj_id = MB_Prog_GYXX.c_proj_id),
(select c_edt from mb_proj where mb_proj.proj_id = MB_Prog_GYXX.c_proj_id),
yssj,
c_proj_c48,
c_proj_c49,
c_proj_c50,
cysj,
c_proj_c51,
c_proj_c52,
c_proj_b4,
c_proj_b5,
c_proj_b8,
c_proj_b9,
c_proj_b10
from MB_Prog_GYXX
select * from v_MB_Prog_v1;
drop view v_mb_prog_v2;
create view v_MB_Prog_v2
(
c_proj_id,
c_proj_year,
c_zonename,
c_proj_c33,
c_year,
c_proj_fsum,
c_proj_f1,
c_proj_f2,
c_proj_f3_4,
c_proj_f5,
c_proj_f6,
c_proj_f7,
c_proj_f11,
c_proj_c39,
c_proj_c18,
c_proj_c11,
c_proj_c12,
c_proj_c40,
c_proj_c24_25,
c_proj_c1_2,
c_proj_c9_10,
c_proj_c3_4,
c_proj_c41,
c_proj_c16,
c_proj_c17,
c_proj_c42,
c_proj_c27,
c_proj_c28,
c_proj_c29,
c_proj_c30,
c_proj_c43,
c_proj_c44,
c_proj_c31,
c_proj_c32,
c_proj_c45,
c_proj_c26,
c_proj_c20,
c_proj_c21,
c_proj_c23,
c_proj_c46,
bz,s1,s2,
bdt,
edt,
yssj,
c_proj_c48,
c_proj_c49,
c_proj_c50,
cysj,
c_proj_c51,
c_proj_c52,
c_proj_b4,
c_proj_b5,
c_proj_b8,
c_proj_b9,
c_proj_b10
)
as
select
t1.c_proj_id,
t1.c_proj_year,
t1.c_zonename,
t1.c_proj_c33,
year(t1.c_dt) as c_year,
isnull(t1.c_proj_f1+t1.c_proj_f2+t1.c_proj_f3+t1.c_proj_f4+t1.c_proj_f5+t1.c_proj_f6+t1.c_proj_f7,0) as c_proj_fsum,
isnull(t1.c_proj_f1,0) as c_proj_f1,
isnull(t1.c_proj_f2,0) as c_proj_f2,
isnull(t1.c_proj_f3+t1.c_proj_f4,0) as c_proj_f3_4,
isnull(t1.c_proj_f5,0) as c_proj_f5,
isnull(t1.c_proj_f6,0) as c_proj_f6,
isnull(t1.c_proj_f7,0) as c_proj_f7,
isnull(t1.c_proj_f11,0) as c_proj_f11,
isnull(t1.c_proj_c39,0) as c_proj_c39 ,
isnull(t1.c_proj_c18,0) as c_proj_c18,
isnull(t1.c_proj_c11,0) as c_proj_c11,
isnull(t1.c_proj_c12,0) as c_proj_c12,
isnull(t1.c_proj_c40,0) as c_proj_c40,
isnull(t1.c_proj_c24+t1.c_proj_c25,0) as c_proj_c24_25,
isnull(t1.c_proj_c1+t1.c_proj_c2,0) as c_proj_c1_2,
isnull(t1.c_proj_c9+t1.c_proj_c10,0) as c_proj_cf9_10,
isnull(t1.c_proj_c3+t1.c_proj_c4,0) as c_proj_c3_4,
isnull(t1.c_proj_c41,0) as c_proj_c41,
isnull(t1.c_proj_c16,0) as c_proj_c16,
isnull(t1.c_proj_c17,0) as c_proj_c17,
isnull(t1.c_proj_c42,0) as c_proj_c42,
isnull(t1.c_proj_c27,0) as c_proj_c27,
isnull(t1.c_proj_c28,0) as c_proj_c28,
isnull(t1.c_proj_c29,0) as c_proj_c29,
isnull(t1.c_proj_c30,0) as c_proj_c30,
isnull(t1.c_proj_c43,0) as c_proj_c43,
isnull(t1.c_proj_c44,0) as c_proj_c44,
isnull(t1.c_proj_c31,0) as c_proj_c31,
isnull(t1.c_proj_c32,0) as c_proj_c32,
isnull(t1.c_proj_c45,0) as c_proj_c45,
isnull(t1.c_proj_c26,0) as c_proj_c26,
isnull(t1.c_proj_c20,0) as c_proj_c20,
isnull(t1.c_proj_c21,0) as c_proj_c21,
isnull(t1.c_proj_c23,0) as c_proj_c23,
isnull(t1.c_proj_c46,0) as c_proj_c46,
'3',
(select proj_type from mb_proj where mb_proj.proj_id = t1.c_proj_id) ,
(select proj_pc from mb_proj where mb_proj.proj_id = t1.c_proj_id),
(select c_bdt from mb_proj where mb_proj.proj_id = t1.c_proj_id) ,
(select c_edt from mb_proj where mb_proj.proj_id = t1.c_proj_id) ,
t1.yssj,
t1.c_proj_c48,
t1.c_proj_c49,
t1.c_proj_c50,
t1.cysj,
t1.c_proj_c51,
t1.c_proj_c52,
t1.c_proj_b4,
t1.c_proj_b5,
t1.c_proj_b8,
t1.c_proj_b9,
t1.c_proj_b10
from mb_prog t1
where c_dt in (select max(t2.c_dt) from mb_prog t2 where t1.c_proj_id=t2.c_proj_id and year(t1.c_dt) = year(t2.c_dt))
select * from v_mb_prog_v2;