表一:
name | type |
A1 | 02 |
A1 | 03 |
A2 | 02 |
A3 | 03 |
name | type02 | type03 |
A1 | 是 | 是 |
A2 | 是 | 否 |
A3 | 否 | 是 |
将表一的结果转为表二有下面这几种方法。
--第一种方法
select t.code,
t.name,
decode(p1.type_code, '02', '是', '否') as 指标单位,
decode(p1.type_code, '03', '是', '否') as 决算单位
from base_unit t
left join base_unit_type p1 on t.guid = p1.unit_guid
and p1.type_code = '02'
left join base_unit_type p2 on t.guid = p2.unit_guid
and p2.type_code = '03'
where (p1.type_code is not null or p2.type_code is not null)
order by p1.type_code, p2.type_code, t.code;
--第二:
select * from (
select code, name, nvl((select '是' from base_unit_type a where a.unit_guid = t.guid
and a.type_code = '02'), '否') as 指标单位,
nvl((select '是' from base_unit_type a where a.unit_guid = t.guid
and a.type_code = '03'), '否') as 决算单位
from base_unit t
where exists (select 1s
from base_unit_type p
where p.unit_guid = t.guid
and p.type_code in ('02', '03')) ) m
order by m.指标单位 desc , m.决算单位 desc, m.code ;
--第三:
select t.code, t.name, max(case when t.type_code='02' then '是' else '否' end) as aa,
max(case when t.type_code='03' then '是' else '否' end) as bb
from
(
select t1.code, t1.name, t2.type_code
from base_unit t1, base_unit_type t2
where t1.guid = t2.unit_guid
and t2.type_code in ('02', '03')
) t
group by t.code,t.name
order by aa desc ,bb desc
--第四:
select m.code,m.name, decode(aa,1,'index_unit','NO'),decode(bb,1,'unit','NO') from (
select k.code,k.name, max(index_unit) as aa,max(unit) as bb from (
select code, name, 1 as index_unit, 0 as unit
from base_unit t
where exists (select 1
from base_unit_type p
where p.unit_guid = t.guid
and p.type_code = '02')
union all
select code, name, 0 as index_unit, 1 as unit
from base_unit t
where exists (select 1
from base_unit_type p
where p.unit_guid = t.guid
and p.type_code = '03')
) k
group by k.code, k.name
) m
order by m.aa+m.bb desc, m.aa, m.code