在单个查询中分层检索记录

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层(甚至可以有子内容)在这种情况下它应该是这样的

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

这是一个典型的递归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;

很像这个:

• 基于父
的子值总和

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