我目前正在設計一個目錄系統。它有兩種型別的專案：類別和專案本身。也可能存在嵌套類別，并且某些專案可能具有始終是某個類別的父項。
所以表格看起來像：
create table items
(
id uuid,
parent uuid,
name text,
type text,
cost int,
primary key (id),
constraint constraint_on_parent
foreign key (parent)
references items (id)
);
另一件要提到的是，所有類別專案的成本都等于 null（好吧，畢竟，您不能購買類別本身，對嗎？）。
現在我需要提出一個 sql 查詢，如果它是一個類別，它會在給定專案 id 的情況下回傳自身及其所有子項。另外，如果它是一個類別，那么我想獲得其子類別的平均價格（也適用于子類別）。
到目前為止，我已經設法創建了一個遞回查詢來檢索它自己和孩子：
with recursive query as (
select id, name, type, cost
from items
where id=$item_id
union all
select it.id, it.name, it.type, it.cost
from items it inner join query q on q.id = it.parent
)
select id, name
from children
但是，現在我想知道，如何更改它以遞回計算類別及其子類別的平均價格？
另外，我使用的是 PostgreSQL 14。
編輯 1：樣本資料和所需的輸出
樣本資料
| ID | 父母 | 姓名 | 型別 | 成本 |
|---|---|---|---|---|
| uuid1 | 無效的 | 根 | 類別 | 無效的 |
| uuid2 | uuid1 | 專案1 | 物品 | 100 |
| uuid3 | uuid1 | 子類 1 | 類別 | 無效的 |
| uuid4 | uuid3 | 專案2 | 物品 | 200 |
| uuid5 | uuid3 | 第 3 項 | 物品 | 300 |
期望的輸出當對uuid3
運行描述的查詢時，我希望得到類似的東西
| ID | 父母 | 姓名 | 型別 | 成本 |
|---|---|---|---|---|
| uuid3 | uuid1 | 子類 1 | 類別 | 250 |
| uuid4 | uuid3 | 專案2 | 物品 | 200 |
| uuid5 | uuid3 | 第 3 項 | 物品 | 300 |
此輸出看起來像樣本資料的最后 3 行，除了第一行，即類別，其價格等于其子項的平均價格當對uuid1
運行所描述的查詢時，我希望得到
| ID | 父母 | 姓名 | 型別 | 成本 |
|---|---|---|---|---|
| uuid1 | 無效的 | 根 | 類別 | 200 |
| uuid2 | uuid1 | 專案1 | 物品 | 100 |
| uuid3 | uuid1 | 子類 1 | 類別 | 250 |
| uuid4 | uuid3 | 專案2 | 物品 | 200 |
| uuid5 | uuid3 | 第 3 項 | 物品 | 300 |
這里subcategory1價格是item2和item3成本的平均值，價格root是和成本的平均值。
此外，如果該類別中沒有專案，則其價格應保持為空item1item2items3
uj5u.com熱心網友回復：
您可以使用 ARRAY 存盤專案的路徑
with recursive query as (
select id, name, type, cost, array[id] path
from items
where id='uuid1'
union all
select it.id, it.name, it.type, it.cost, array_append(path, it.id)
from items it inner join query q on q.id = it.parent
)
select t1.id, t1.name, t1.type, avg(t2.cost) cost
from query t1
left join query t2 on t2.path @> Array[t1.id]
group by t1.id, t1.name, t1.type;
db<>小提琴
退貨
id name type cost
uuid1 root category 200.0000000000000000
uuid2 item1 item 100.0000000000000000
uuid3 subcategory1 category 250.0000000000000000
uuid4 item2 item 200.0000000000000000
uuid5 item3 item 300.0000000000000000
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/489058.html
標籤：sql PostgreSQL 递归 公共表表达式 窗函数
下一篇：遞回遍歷嵌套的HTML串列