sql - 在单个查询中对记录进行分层检索

Question

我正在使用 PostgreSQL 9.2。我有一个存储产品信息的产品表，它的子表是内容表，其中包含与其链接的产品。

假设如果我在主表中有一个名为 Burger 的产品，那么我将拥有它的内容产品如

 bread,cheese,chilli.

他们可以是这样一种场景，面包将是主要产品，其内容物将是面粉和盐等。

我正在编写一个查询来检索所有产品(与它们关联的 ID)及其内容产品，这些产品应该显示类似这种层次结构

Burger----bread--+----flour
                 +----salt
      ----cheese
      ----chilli

我必须得到这样的结果

burger bread
burger cheese
burger chilli
bread  flour
bread  salt

这个层次结构可以运行到 n 级(比如即使是 floor 也可以有子内容，在这种情况下它应该是这样的

burger bread
burger cheese
burger chilli
bread  flour
bread  salt
flour  someprod1
flour someprod2   assuming if someprod1 and someprod2 are the contents)

我写了以下查询:

select rec.m_product_id,rec.m_productbom_id 
from rec_product_recipe  rec
join rec_product_recipe rec1
on rec1.m_productbom_id = rec.m_product_id

但这在一个层面上显示为:

burger bread
burger cheese
burger chilli

Answer 1

这是 recursive CTE 的教科书示例:

WITH RECURSIVE cte AS (
   SELECT 0 AS lvl, m_product_id, m_productbom_id
   FROM   rec_product_recipe
   WHERE  m_product_id = <id of burger> -- restrict to one root product

   UNION ALL
   SELECT c.lvl + 1, r.m_product_id, r.m_productbom_id
   FROM   cte  c
   JOIN   rec_product_recipe r ON r.m_product_id = c.m_productbom_id
   -- WHERE c.lvl < 10   -- protection against loops (optional)
   )
SELECT *
FROM   cte
ORDER  BY 1,2,3;

很像这个:

• Total children values based on parent

如果你有循环依赖，查询将陷入无限循环并最终引发异常。如果发生这种情况，请添加某种中断条件。就像我作为评论添加的最大迭代级别。或用于外部 SELECT 的 LIMIT n，这也会使 CTE 在检索到足够的行后立即停止循环。 Examples in the manual .