試圖計算孩子的行數

我正在嘗試獲取父行的子級計數。我如下使用的 sql 查詢只提取即時計數，我怎樣才能獲得總計數，因為孩子可以有更多的孩子。

select  c.id, c.base32_id, c.author, c.message, c.title, c.type, c.datetime,
(select count(*) from comment where parent_id= c.id ) as ChildCount
from comment c where c.parent_id is null order by id asc limit 25  

http://sqlfiddle.com/#!15/158ea

您需要首先使用遞歸公用表表達式檢索整個評論樹。然後您可以計算每個根節點的子節點數：

with recursive all_comments as (
  select id, parent_id, id as root_id
  from comment
  where parent_id is null
  union all
  select c.id, c.parent_id, p.root_id
  from comment c
    join all_comments p on c.parent_id = p.id
)
select root_id, count(*) as comment_count
from all_comments
group by root_id;

SQLFiddle：http ://sqlfiddle.com/#!15/158ea/15

如果您不想獲取每個“執行緒”的計數，但對於每個節點，您可以使用以下內容：

with recursive all_comments as (
  select id, parent_id, id as root_id, 1 as level
  from comment
  where parent_id is null
  union all
  select c.id, c.parent_id, p.root_id, p.level + 1
  from comment c
    join all_comments p on c.parent_id = p.id
)
select ac.id, 
      ac.parent_id,
      ac.root_id,
      count(*) over (partition by ac.root_id) - count(*) over (partition by ac.root_id order by level) as child_count
from all_comments ac
order by ac.id;

SQLFiddle：http ://sqlfiddle.com/#!15/158ea/17

以上是使用視窗函式計算根節點的總數或節點數以及每個級別的節點數：

count(*) over (partition by ac.root_id)計算每個根節點的子節點（包括根節點）的數量。

count(*) over (partition by ac.root_id order by level)計算目前節點級別的節點數*。*總計數與“到現在”的計數之間的差異是子節點的數量。

---

順便說一句：comment是一個 SQL 關鍵字，您不應該將其用作表名。

引用自：https://dba.stackexchange.com/questions/110486