对于递归查询,您可以使用此技巧和 0 填充的字符串创建分层路径:SQL小提琴 http://www.sqlfiddle.com/#!12/77771/12/0
with recursive comment_list(article_comment_id, parent_comment_id, comment, article_id, comment_depth, comment_path) AS (
select c.article_comment_id,
c.parent_comment_id,
c.comment,
c.article_id,
c.comment_depth,
substr(CAST(1000000000+c.article_comment_id as varchar(1000)),2)
from test_comment c
where article_id = 100
and parent_comment_id = 0
union all
select c.article_comment_id,
c.parent_comment_id,
c.comment,
c.article_id,
c.comment_depth,
cl.comment_path || substr(CAST(1000000000+c.article_comment_id as varchar(1000)),2)
from test_comment c
join comment_list cl on c.parent_comment_id = cl.article_comment_id
)
select cl.article_comment_id,
cl.comment_path,
cl.parent_comment_id,
cl.comment,
cl.article_id,
cl.comment_depth
from comment_list cl
order by cl.comment_path, cl.article_comment_id, cl.comment_depth;
删除 GROUP BY。您想要将它们“分组”以进行显示,这实际上是“ORDER BY”
select cl.parent_comment_id,
cl.article_comment_id,
cl.comment,
cl.article_id,
cl.comment_depth
from comment_list cl
order by cl.parent_comment_id, cl.article_comment_id, cl.comment_depth;
您可能仍然需要也可能不需要cl.root_id
按顺序排列,所以可以是
order by cl.root_id, cl.parent_comment_id, cl.article_comment_id, cl.comment_depth;