样例如下
create or replace view asset_ref_zcbb as
select k.zc_code, f1.name zc_type1, f2.name zc_type2, f3.name zc_name,
k.gz_date, k.gz_price, k.pk_org,
o1.name org_name,
case k.zc_performance
when '1' then '优'
when '2' then '良'
when '3' then '一般'
when '4' then '差'
else '' end zc_performance,
case k.is_guzi
when '1' then '是'
when '2' then '否'
else '' end is_guzi,
case k.dr
when 1 then '已转让'
else (case k.zc_state
when '1' then '未领用'
when '2' then '在用'
else '已处置' end) end zc_state,
case k.zc_source
when '1' then '新购'
else '受让' end zc_source,
o2.name bg_department,
u.user_name bg_person
from asset_ref_zckc k
inner join assets_ref_zcfl f1 on k.zc_type1 = f1.pk_defdoc
inner join assets_ref_zcfl f2 on k.zc_type2 = f2.pk_defdoc
inner join assets_ref_zcfl f3 on k.zc_name = f3.pk_defdoc
inner join org_orgs o1 on k.pk_org = o1.pk_org
left join org_orgs o2 on k.bg_department = o2.pk_org
left join sm_user u on k.bg_person = u.cuserid;
select k.zc_code, f1.name zc_type1, f2.name zc_type2, f3.name zc_name,
k.gz_date, k.gz_price, k.pk_org,
o1.name org_name,
case k.zc_performance
when '1' then '优'
when '2' then '良'
when '3' then '一般'
when '4' then '差'
else '' end zc_performance,
case k.is_guzi
when '1' then '是'
when '2' then '否'
else '' end is_guzi,
case k.dr
when 1 then '已转让'
else (case k.zc_state
when '1' then '未领用'
when '2' then '在用'
else '已处置' end) end zc_state,
case k.zc_source
when '1' then '新购'
else '受让' end zc_source,
o2.name bg_department,
u.user_name bg_person
from asset_ref_zckc k
inner join assets_ref_zcfl f1 on k.zc_type1 = f1.pk_defdoc
inner join assets_ref_zcfl f2 on k.zc_type2 = f2.pk_defdoc
inner join assets_ref_zcfl f3 on k.zc_name = f3.pk_defdoc
inner join org_orgs o1 on k.pk_org = o1.pk_org
left join org_orgs o2 on k.bg_department = o2.pk_org
left join sm_user u on k.bg_person = u.cuserid;