sql - 具有递归和默认值的树

Question

使用 Postgres。

我有一个价格表

CREATE TABLE pricelists(
  id SERIAL PRIMARY KEY,
  name TEXT,
  parent_id INTEGER REFERENCES pricelists
);

还有另一个表，prices，引用它

CREATE TABLE prices(
  pricelist_id INTEGER REFERENCES pricelists,
  name TEXT,
  value INTEGER NOT NULL,
  PRIMARY KEY (pricelist_id, name)
);

• 父价目表 id=1 可能有 10 个价格。
• 价目表 id=2 作为父级 1 的子级可能有 5 个价格覆盖相同价格名称的父级 1 价格。
• 子价目表 id=3 作为价目表 2 的子项可能有 2 个价格覆盖相同价格名称的子 2 价格.

因此，当我询问 child 3 的价格时，我想得到

• 3 和
child 的所有价格
• 他 parent ( child 2)的那些价格不存在于 child 3 和
• 直到现在还不存在的所有父 1 价格。

可以更改架构以提高效率。

示例:

如果

SELECT pl.id AS id, pl.parent_id AS parent, p.name AS price_name, value
FROM pricelists pl
JOIN prices p ON pl.id = p.pricelist_id;

给予

| id       |      parent   |  price_name |     value   |  
|----------|:-------------:|------------:|------------:|  
| 1        |  1            | bb          |     10      |  
| 1        |  1            | cc          |     10      |  
| 2        |  1            | aa          |     20      |  
| 2        |  1            | bb          |     20      |  
| 3        |  2            | aa          |     30      |

然后我正在寻找一种获取 pricelist_id = 3 价格的方法

| id       |      parent   |  price_name |     value   |  
|----------|:-------------:|------------:|------------:|  
| 1        |  1            | cc          |     10      |  
| 2        |  1            | bb          |     20      |  
| 3        |  2            | aa          |     30      |

Answer 1

WITH RECURSIVE cte AS (
   SELECT id, name, parent_id, 1 AS lvl
   FROM   pricelists
   WHERE  id = 3  -- provide your id here

   UNION ALL
   SELECT pl.id, pl.name, pl.parent_id, c.lvl + 1
   FROM   cte  c
   JOIN   pricelists pl ON pl.id = c.parent_id
   )
SELECT DISTINCT ON (p.price_name)
       c.id, c.parent_id, p.price_name, p.value
FROM   cte c
JOIN   prices p ON p.pricelist_id = c.id
ORDER  BY p.price_name, c.lvl;  -- lower lvl beats higher level

• 使用 recursive CTE像这里:

  • Total children values based on parent
  • Recursive SELECT query to return rates of arbitrary depth?

  有很多相关的答案。

• 最后加入一次价格，比较便宜。

• 使用 DISTINCT ON 获得“每组最大”:

  • Select first row in each GROUP BY group?