终于还是做出来了,联合多表,做成视图,内层Repeater用一个Repeater实现了。贴出视图SQL代码:--视图一 获取固有信息里的统计字段 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;
建议把可以用linq把查询出来的数据归纳为和"此表格的一行数据作为一个成员"的集合,若数据量不大完全可以在内存里就组装好这个数据源的模型直接使用一个repeat绑定.
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;
--百分比视图
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=2select * 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_v1union all
select * from v_MB_Prog_v2union all
select * from v_MB_Prog_v3select * from v_mb_prog_v4;