oracle,pg根据子id找到最终父id ,放开parent_id = '0' 看到所有父id

根据子id找到最终父id ,放开parent_id = '0' 看到所有父id

WITH RECURSIVE dict AS (
     SELECT *
     FROM category
     WHERE id= '6'
     union ALL
     SELECT category.*
     FROM category,
          dict
     WHERE category.id = dict.parent_id
)
SELECT id AS id, name as name, parent_id as parentId
FROM dict where parent_id = '0'
ORDER BY id