postgresql - 列表与子列表 postgresql 分组

Question

这是一个我不怎么搜索甚至描述的问题。但我会试一试。

一开始我有 2 个表:

CREATE TABLE vch
    (vchid int4, subject text);    
INSERT INTO vch
    (vchid, subject)
VALUES
    (1, 'Volvo'),
    (2, 'Ford'),
    (3, 'Jeep'),
    (4, 'Toyota');

CREATE TABLE rec
    (recid int4, recvch int4, recname text);    
INSERT INTO rec
    (recid, recvch, recname)
VALUES
    (1000, 1,'xxx'),
    (2000, 1,'yyy'),
    (3000, 3,'zzz'),
    (4000, 4,'aaa');

我的目标是创建如下所示的输出:

1 Volvo
{
1000 xxx
2000 yyy
}
3 Jeep
{
3000 zzz 
}
4 Toyota
{
4000 aaa
}

它看起来与json相关，但格式如上。

我最接近的是这个:

SELECT vchid, 0 as id, subject FROM vch
UNION ALL
SELECT recvch, recid, recname FROM rec
ORDER BY 1 

sqlfiddle:http://sqlfiddle.com/#!15/d3a61d/12

Answer 1

这给出一行结果，文本输出除以换行符:

select
    string_agg(
        format(
            e'%s %s\n{\n%s\n}',
            vchid, subject, rec
        ),
        e'\n' order by 1
    ) as result
from (
    select
        vchid,
        subject,
        string_agg(
            format(
                e'%s %s ',
                recid, recname
            ),
            e'\n'
        ) as rec
    from vch
    join rec on vchid = recvch
    group by 1, 2
    ) s;

结果:

  result   
-----------
 1 Volvo  +
 {        +
 1000 xxx +
 2000 yyy +
 }        +
 3 Jeep   +
 {        +
 3000 zzz +
 }        +
 4 Toyota +
 {        +
 4000 aaa +
 }
(1 row)

您可以将 unnest(string_to_array()) 添加到上述查询中，以将每一行放在一行中:

select 
    unnest(string_to_array(result, e'\n')) as result
from (  
    select
        string_agg(
            format(
                e'%s %s\n{\n%s\n}',
                vchid, subject, rec
            ),
            e'\n' order by 1
        ) as result
    from (
        select
            vchid,
            subject,
            string_agg(
                format(
                    e'%s %s ',
                    recid, recname
                ),
                e'\n'
            ) as rec
        from vch
        join rec on vchid = recvch
        group by 1, 2
        ) s
    ) s;

结果:

  result   
-----------
 1 Volvo
 {
 1000 xxx 
 2000 yyy 
 }
 3 Jeep
 {
 3000 zzz 
 }
 4 Toyota
 {
 4000 aaa 
 }
(13 rows)