sql - 如何在Postgresql中使用条件递归获取记录？

Question

我有两个表:

位置:

locationid   parentid
  1
  2              1
  3              1
  4              2
  5              4
  8              1

批准日志:

locationid  approved
   4          True
   8          True

我需要编写一个查询来提供特定位置的所有子位置，但如果 locationid 已获批准，则忽略它及其子项，即使子项未获批准。

简而言之，当遇到具有approved=True 的locationid 时，忽略它及其所有子项(意味着停止递归该分支)。

例如:

• 对于 locationid=2 我想得到:

  2
  

• 对于 locationid=8 我想得到:

  Nothing
  

• 对于 locationid=1 我想得到:

  1,2,3
  

  4 已获批准，因此请忽略它及其子项。 8 已获批准，请忽略它。

这是我的代码:

with recursive location_tree as (
   select  locationid, parentid
   from locations
   where locationid = 1 and not approved
   union all
   select child.locationid, child.parentid
   from locations child
   join location_tree parent on parent.locationid = child.parentid
   where child.active
)
select array_agg(locationid) 
from location_tree

这只是给出了一个位置列表。

基本上我需要的是递归的“停止条件”。我如何更改它才能工作？

有人可以帮忙吗？

Answer 1

这是我在 PostgreSQL 中用来获取父项及其未经批准的子项的方法，假设父项本身也未获批准:

WITH recursive location_tree AS (
  --PARENT
  SELECT
    p.locationid,
    p.parentid
  FROM
    locations p
  LEFT JOIN
    approvelog pa ON pa.locationid = p.locationid AND pa.approved = TRUE
  WHERE
    p.locationid = 1
    AND pa.locationid IS NULL --Exclude approved parent
  UNION ALL

  --CHILD
  SELECT 
    c.locationid,
    c.parentid
  FROM 
    locations c
  JOIN 
    location_tree p on p.locationid = c.parentid
  LEFT JOIN
    approvelog ca ON ca.locationid = c.locationid AND ca.approved = TRUE
  WHERE
    ca.locationid IS NULL --Exclude approved children
)
SELECT 
  array_agg(locationid) 
FROM 
  location_tree

SQL fiddle :http://sqlfiddle.com/#!15/7084f/19