PG CTE 递归 SQL 翻译为 达梦版本

PG SQL

with recursive result
as 
(select res_id,phy_res_code,res_name from tbl_res where parent_res_id =  (select res_id from tbl_res where phy_res_code = 'org@96000#20211203155858') and res_type_id = 1 union all select t1.res_id, t1.phy_res_code, t1.res_name from tbl_res t1, result where t1.parent_res_id=result.res_id and res_type_id = 1
)
select res_id,phy_res_code,res_name 
from result 
where res_id <> (select res_id from tbl_res where phy_res_code = 'org@96000#20211203155858');

DM SQL

with result(res_id, phy_res_code, res_name)
as 
(select res_id,phy_res_code,res_name from tbl_res where parent_res_id =  (select res_id from tbl_res where phy_res_code = 'org@96000#20211203155858') and res_type_id = 1 union all select t1.res_id, t1.phy_res_code, t1.res_name from tbl_res t1, result where t1.parent_res_id=result.res_id and res_type_id = 1
)
select res_id,phy_res_code,res_name 
from result 
where res_id <> (select res_id from tbl_res where phy_res_code = 'org@96000#20211203155858');

总结

• 将 recursive 关键字删除了
• 临时表 result 后面必须补充上表的字段列表 (column1 , column2, xxx)