我有一張ecosystems表，它是自參考的，因為每個生態系統都可以有子生態系統。每個生態系統也可以有一個分數（代表生態系統的健康程度）。列是（帶有示例資料）：
| slug | parent_slug | full_slug | score |
| ---- | ----------- | ----------- | ----- |
| aaa | null | aaa | 1 |
| bbb | aaa | aaa/bbb | 2 |
| ccc | bbb | aaa/bbb/ccc | 4 |
| ddd | null | ddd | 8 |
| eee | ddd | ddd/eee | 16 |
| fff | null | fff | 32 |
該full_slug列代表從頂級生態系統向下的完整路徑。它是多余的，因為它可以從slug和parent_slug列中推斷出來，但它就在那里。
我想要實作的是創建一個具有相同行數的查詢，但有一列total_score遞回地計算每個生態系統的分數加上其所有子生態系統的分數。也就是說，輸出應該是：
| slug | total_score |
| ---- | ----------- |
| aaa | 7 |
| bbb | 6 |
| ccc | 4 |
| ddd | 24 |
| eee | 16 |
| fff | 32 |
我開始了以下查詢：
WITH top AS (
SELECT
SUM(e.score) as total_score,
CASE instr(e.full_slug, '/') WHEN 0 THEN
e.full_slug
ELSE
substr(e.full_slug, 0, instr(e.full_slug, '/'))
END AS top_level_eco
FROM ecosystems e
GROUP BY top_level_eco
)
SELECT
e.slug,
top.total_score
FROM ecosystems e
INNER JOIN top on top.top_level_eco = e.slug;
但不幸的是，它只顯示了頂級生態系統及其總分。
uj5u.com熱心網友回復：
我能想到幾個答案...
一般的答案是使用遞回......
WITH
tree AS
(
SELECT
slug AS base_slug,
slug AS current_slug,
score AS score
FROM
ecosystems
UNION ALL
SELECT
t.base_slug,
e.slug,
e.score
FROM
tree t
INNER JOIN
ecosystems e
ON e.parent_slug = t.current_slug
)
SELECT
base_slug AS slug,
SUM(score) AS total_score
FROM
tree
GROUP BY
base_slug
ORDER BY
base_slug
另一種選擇是使用full_slugin a JOIN，盡管這將禁止使用索引，并且通常比上面的一般解決方案慢得多。
SELECT
e.slug,
SUM(m.score) AS total_score
FROM
ecosystems e
INNER JOIN
ecosystems m -- members
ON '/' || m.full_slug || '/' LIKE '%/' || e.slug || '/%'
GROUP BY
e.slug
ORDER BY
e.slug
第三種方法是 to unnest/ explodethe full_slug （即為 的每個組件創建一行full_slug），然后按組件分組。SQLite 本身沒有該功能，因此也可能通過遞回解決。
WITH
tree AS
(
SELECT
SUBSTR(full_slug || '/', 1, INSTR(full_slug || '/', '/')-1) AS slug,
SUBSTR(full_slug || '/', INSTR(full_slug || '/', '/') 1) AS path,
score
FROM
ecosystems
UNION ALL
SELECT
SUBSTR(path, 1, INSTR(path, '/')-1) AS slug,
SUBSTR(path, INSTR(path, '/') 1) AS path,
score
FROM
tree
WHERE
tree.path <> ''
)
SELECT
slug,
SUM(score) AS total_score
FROM
tree
GROUP BY
slug
ORDER BY
slug
所有三種方法的演示：
- https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4e535c238823843c3c27cb8ec617c974
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/325067.html