递归选择? [英] Recursive select?
问题描述
我具有以下表结构:
因此每个论坛帖子都有一个父母,也有一个父母(根帖子除外),等等。
我需要的是要获得论坛帖子拥有的孩子总数,包括他的孩子的孩子,孙子的孩子等等。
现在,我有一个简单的选择,可以返回直系孩子:
从论坛帖子
中选择count(*)作为child_count
,其中parent_forum_post_id = $ criteria.fid
我什至不确定这可以通过sql来实现,但是我是SQL的入门者,所以我想也许有人可以提出一些想法。
任何帮助都是值得的。谢谢。
应该这样做:
具有递归all_posts(id,parentid,root_id)作为
(
选择t1.id,
t1.parent_forum_post_id作为父母ID,
t1.id作为root_id来自论坛帖子t1
的
,其中t1.parent_forum_post_id为空
合并所有
选择c1.id,
c1.parent_forum_post_id作为父代,
p.root_id
来自论坛帖子c1
加入all_posts p.id = c1.parent_forum_post_id
)
选择root_id,count(*)
从all_posts
由root_id排序;
您可以通过在条件t1处修改条件来更改开始点。 parent_forum_post_id为空
。
I have the following table structure:
So each forum post has a parent, who also has a parent(except the root posts), etc. What I need is to get the total number of children a forumpost has, including his children's children, grandchildren's children and so on.
For now I have a simple select that returns the immediate children:
select count(*) as child_count
from forumposts
where parent_forum_post_id = $criteria.fid
I'm not even sure this is doable via sql, but I'm a begginer in SQL so I thought maybe someone can give some ideas.
Any help is appreciated. Thanks.
This should do it:
with recursive all_posts (id, parentid, root_id) as
(
select t1.id,
t1.parent_forum_post_id as parentid,
t1.id as root_id
from forumposts t1
where t1.parent_forum_post_id is null
union all
select c1.id,
c1.parent_forum_post_id as parentid,
p.root_id
from forumposts c1
join all_posts p on p.id = c1.parent_forum_post_id
)
select root_id, count(*)
from all_posts
order by root_id;
You can change the "starting" point by modifying the condition where t1.parent_forum_post_id is null
.
这篇关于递归选择?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!