常用查询

表一：

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