最近在进行项目的去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同理。