You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
create table posts (
id int primary key,
title text,
tags text,
body text,
parentid int
);
create table comments (
id int primary key,
postid int,
ans text
);
insert into posts values
(1,'标题1','标签1','问题1',null),
(2,'标题2','标签2','问题2',null),
(3,null,null,'问题1回答1',1),
(4,null,null,'问题1回答2',1),
(5,'标题3','标签3','问题3',null);
insert into comments values
(1,1,'问题1的留言1'),
(2,1,'问题1的留言2'),
(3,3,'问题1回答1的留言1'),
(4,4,'问题1回答2的留言1'),
(5,4,'问题1回答2的留言2');
create or replace function get_liuyan(int) returns text[] as $$
select array_agg(ans) from comments where postid=$1;
$$ language sql;
with recursive tmp as (
select id, title, tags, body, get_liuyan(ID) as liuyan, '' as huida, '{}'::text[] as huida_liuyan, 1 as level from
posts where parentid is null
union all
select posts.id, coalesce(posts.title,tmp.title), coalesce(posts.tags,tmp.tags), tmp.body, get_liuyan(posts.ID), posts.body, get_liuyan(posts.id) , tmp.level+1 from
tmp join posts on (posts.parentid=tmp.id)
)
select title,tags,body,max(liuyan) filter (where level=1),jsonb_object_agg(huida,huida_liuyan) filter (where level>1) from tmp
group by title,tags,body;
posts表
comments表
表现形式:
标题
标签
问题1
留言1
留言2
回答1
回答1留言1
回答1留言2
回答2
回答2留言1
bilili来,表结构大致如上,请大神帮忙PostgreSQL单条问题查询语句如何写。希望格式 。希望title(标题),tags(标签),body(问题),comments(问题留言数组),answers(回答数组,comments:回答留言数组嵌套在answer里面)。感觉要递归嵌套,比较复杂无从下手。
The text was updated successfully, but these errors were encountered: