2020-12-30工作日志

最近在进行项目的去O工作，就是去除Oracle改成PG库的任务。主要就是要把项目国产化，这一系列工作中涉及到许多SQL改造等问题。今天记录一个union all的问题：
更改后的sql：

with temp as
         (select t.catalog_id, t."CATALOG_NAME", t.parent_catalog_id, t.catalog_level::TEXT,CAST('0' AS TEXT) AS CARD_TYPE, CAST('0' AS TEXT) AS nodetp
          from OSBOOT_DCM_CARD_CATALOG t
          where t.status = 1 and t.catalog_level<>0
          union all
          select distinct t2.card_id, t2.card_name, t2.catalog_id, CAST('3' AS TEXT) AS "TEXT",t2.CARD_TYPE::TEXT, CAST('1' AS TEXT) AS nodetp
          from Osboot_Dcm_Card_Market t2
          where t2.status = 2 or (t2.status = 1 and t2.CREATE_USER='admin'
              and t2.card_type!=4)),
     temp1 as
         (select t2.catalog_id, count(1) cnt
          from Osboot_Dcm_Card_Market t2
          where (t2.status = 2 or (t2.status = 1 and t2.CREATE_USER='admin' ))  and t2.CARD_TYPE = 1
          group by t2.catalog_id),
     temp2 as
         (select t.parent_catalog_id, sum(cnt) cnt
          from temp t
                   inner join temp1 t1
                              on t.catalog_id = t1.catalog_id
          group by t.parent_catalog_id)
select t.catalog_id,
       t."CATALOG_NAME",
       t.parent_catalog_id,
       t.catalog_level,
       t1.cnt,
       t.CARD_TYPE,
       nodetp
from temp t
         left join (select * from temp1 union all select * from temp2) t1
                   on t.catalog_id = t1.catalog_id
where t.CARD_TYPE :: numeric< 2
order by t.catalog_id, t.parent_catalog_id

注意到union all 是需要将前后查询的字段进行相同类型的转换才能查询出来的。
这个catalog_level原来是int类型 但是其他都是varchar 所以需要类型转换下。下面的card_type同理。