tree.sql

WITH RECURSIVE tree AS (
   SELECT id,
          parent_id,
          name,
          '{}'::varchar[] AS p
     FROM sectors
    WHERE parent_id is null
    UNION
   SELECT e.id,
          e.parent_id,
          e.name,
          array_append(p, e.parent_id) as p
     FROM sectors e
          INNER JOIN tree t ON t.id = e.parent_id
    WHeRE e.parent_id is not null
) SELECT * FROM tree;


 c078f5bf3e3a94c5a12acc7fb63c5126 | f9efb2527d1b3b220f65a204d6fc2f4c | 汽车交易                       | {f9efb2527d1b3b220f65a204d6fc2f4c}
 71d52f5380f823e2a85a1979b742b4a1 | f9efb2527d1b3b220f65a204d6fc2f4c | 汽车后市场                     | {f9efb2527d1b3b220f65a204d6fc2f4c}
 7771319df98913201ad85140fc1634a9 | f9efb2527d1b3b220f65a204d6fc2f4c | 公共交通                       | {f9efb2527d1b3b220f65a204d6fc2f4c}
 3189d4520816f2b8630c5990d555fbb6 | f9efb2527d1b3b220f65a204d6fc2f4c | 车联网                         | {f9efb2527d1b3b220f65a204d6fc2f4c}
 c431eb7a1a2f58f89e796bdae860103f | f9efb2527d1b3b220f65a204d6fc2f4c | 汽车交通支撑服务               | {f9efb2527d1b3b220f65a204d6fc2f4c}
 0e473184e051903bd12596c68139ae45 | 0bc74d33660674625b5876e20402d131 | 通信通讯                       | {0bc74d33660674625b5876e20402d131}
 daa05cd986ec88b28ac84475e82564af | 0bc74d33660674625b5876e20402d131 | 批发零售                       | {0bc74d33660674625b5876e20402d131}
 15795f5cbf4ce670c89f5a0813db3769 | 0bc74d33660674625b5876e20402d131 | 化学化工                       | {0bc74d33660674625b5876e20402d131}
 9b008edc36ce0a3b1408dbd4490edd46 | 0bc74d33660674625b5876e20402d131 | 电子制造                       | {0bc74d33660674625b5876e20402d131}
 a043f6158c6bf4abee06d65b94cf1580 | 0bc74d33660674625b5876e20402d131 | 材料制造                       | {0bc74d33660674625b5876e20402d131}
 352c9c8c46c10ec941b87f58b7e46869 | 0bc74d33660674625b5876e20402d131 | 石油矿采                       | {0bc74d33660674625b5876e20402d131}
 2710eef275fbf2d25ab78e5a7ccadf90 | 0bc74d33660674625b5876e20402d131 | 建筑业                         | {0bc74d33660674625b5876e20402d131}
 0dbac03dd3403e2ae83cc24e0d9e23d1 | 0bc74d33660674625b5876e20402d131 | 公共服务                       | {0bc74d33660674625b5876e20402d131}