需求

一张表里的数据有子节点和父节点，需要得出某个子节点的所有父节点，以及最高的深度。

解决过程

搞清现存问题

到底是查找所有的父节点还是所有的子节点

    SELECT level FROM BOM b
    WHERE level > 10 
    START WITH dom = 'dep1' AND parent_qty = 20
    CONNECT BY PRIOR parent_qty = child_qty  AND  dom = 'dep1'

prior是上一条，也就是说上一条的parent是本条的child，所以是查找父节点。

问题1 recursive

postgres里面没有完全相同的东西，但是可以用recursive来实现
参照的url https://blog.csdn.net/liyuhui195134/article/details/78972764
写的一目了然

它查找所有父节点的sql

 WITH RECURSIVE res AS (
   SELECT t1.* FROM t_code as t1  
   WHERE t1.enable = 'true' and t1.id = '4'
 
   UNION 
     SELECT t2.* from t_code as t2
     INNER JOIN res as t3 ON t2.id = t3.parent_id
     WHERE t2.enable = 'true'
 )SELECT res.* from res

仿照一下，改成自己的

    WITH RECURSIVE res AS (
    SELECT t1.* FROM bom AS t1  
    WHERE t1.dom = 'dep1' AND t1.parent_qty = 20
    
    UNION 
        SELECT t2.* FROM bom AS t2
        INNER JOIN res as t3 ON t2.child_qty = t3.parent_qty
        WHERE t2.dom = 'dep1' 
    )SELECT res.* FROM res

问题2 level

postgres中不能直接像oracle一样得到这个伪列
只能参考这个url https://stackoverflow.com/questions/33957917/how-does-one-print-depth-level-in-a-postgres-query-that-uses-recursive-to-select

简单来说，就是在初始条件中加入0作为t1的depth，然后在递归中不断用t3.depth+1

修改结果如下

    WITH RECURSIVE res AS (
    SELECT t1.* ,0 depth FROM bom AS t1  
    WHERE t1.dom = 'dep1' AND t1.parent_qty = 20
    
    UNION 
        SELECT t2.*,t3.depth+1 FROM bom AS t2
        INNER JOIN res as t3 ON t2.child_qty = t3.parent_qty
        WHERE t2.dom = 'dep1' 
    )SELECT res.* FROM res

问题3 死循环

在没有加入depth计算的时候，即使同一条中parent等于child也没什么问题
但是假如depth计算后，出现了死循环，因为parent也是child，所以。。。。
没有办法，只能加入限制条件

    WITH RECURSIVE res AS (
    SELECT t1.* ,0 depth FROM bom AS t1  
    WHERE t1.dom = 'dep1' AND t1.parent_qty = 20 AND t1.parent_qty <> t1.child_qty
    
    UNION 
        SELECT t2.*,t3.depth+1 FROM bom AS t2
        INNER JOIN res as t3 ON t2.child_qty = t3.parent_qty
        WHERE t2.dom = 'dep1' AND t2.parent_qty <> t2.child_qty
    )SELECT res.* FROM res

小结

文中引用的2个url的东西说的都通俗易懂，其他的结果实在让人看不懂。