postgresql - 如何将多个整数类型的列附加到单个列中

Question

以下是我的查询:-

select 
ou.organisationunitid as one,
ou2.organisationunitid as two,
ou3.organisationunitid as three,
ou4.organisationunitid as four,
ou5.organisationunitid as five,
ou6.organisationunitid as six,
ou7.organisationunitid as seven,
ou8.organisationunitid as eight,
ou9.organisationunitid as nine,
ou10.organisationunitid as ten ,
ou11.organisationunitid as eleven
from orgunitgroupmembers ougm
.
.
.
group by   ou.organisationunitid,ou2.organisationunitid,ou3.organisationunitid,ou4.organisationunitid,ou5.organisationunitid,ou6.organisationunitid,ou7.organisationunitid,ou8.organisationunitid,ou9.organisationunitid,ou10.organisationunitid,ou11.organisationunitid

这是输出:

我想将所有这些整数类型的 ID 合并到一个列中，以便我可以将其提供给另一个查询。例如:select * from orgunit where id in ( 上述查询的输出 )

我尝试使用字符串 concat，但这行不通，因为我需要将它提供给另一个查询。

那么，有没有什么办法可以实现呢？任何帮助将不胜感激。请提出一个解决方案，该解决方案与将生成的单列输出作为输入提供给另一个查询兼容。

注意:我在一个受限的环境中工作，所以我无法使用任何“功能”或插件。

谢谢

好的，主要修改:

这是 fiddle -> https://www.db-fiddle.com/f/tKYF4jeDm8B5cWLdhWav3o/0

我想要做的是，我想要获取 orgunitgroup 成员以及他们在层次结构中的所有子项。 self join之间是父子关系。

同时在此处粘贴创建表和查询:

     CREATE TABLE organisationunit (
  organisationunitid integer,
  parentid integer

);

INSERT INTO organisationunit (organisationunitid,parentid) VALUES (1,NULL);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (2,1);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (3,2);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (4,3);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (5,3);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (6,3);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (7,3);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (8,3);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (9,3);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (10,4);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (11,4);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (12,4);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (13,5);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (14,5);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (15,5);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (16,5);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (17,6);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (18,6);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (19,6);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (20,7);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (21,7);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (22,7);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (23,7);


CREATE TABLE orgunitgroupmember (
  orgunitgroupid integer,
  organisationunitid integer  
);

INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,10);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,11);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,5);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,6);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,8);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,22);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,3);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (2,15);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (3,22);

CREATE TABLE orgunitgroup (
  orgunitgroupid integer
);

INSERT INTO orgunitgroup (orgunitgroupid) VALUES (1);

完整查询:

select 
ou.organisationunitid as one,
ou2.organisationunitid as two,
ou3.organisationunitid as three,
ou4.organisationunitid as four,
ou5.organisationunitid as five,
ou6.organisationunitid as six,
ou7.organisationunitid as seven,
ou8.organisationunitid as eight,
ou9.organisationunitid as nine,
ou10.organisationunitid as ten ,
ou11.organisationunitid as eleven
from orgunitgroupmember ougm
inner join organisationunit ou on ou.organisationunitid =  ougm.organisationunitid
left join organisationunit ou2 on ou.organisationunitid =  ou2.parentid
left join organisationunit ou3 on ou2.organisationunitid =  ou3.parentid
left join organisationunit ou4 on ou3.organisationunitid =  ou4.parentid
left join organisationunit ou5 on ou4.organisationunitid =  ou5.parentid
left join organisationunit ou6 on ou5.organisationunitid =  ou6.parentid
left join organisationunit ou7 on ou5.organisationunitid =  ou7.parentid
left join organisationunit ou8 on ou5.organisationunitid =  ou8.parentid
left join organisationunit ou9 on ou5.organisationunitid =  ou9.parentid
left join organisationunit ou10 on ou5.organisationunitid =  ou10.parentid
left join organisationunit ou11 on ou5.organisationunitid =  ou11.parentid

inner join orgunitgroup oug on oug.orgunitgroupid = ougm.orgunitgroupid
where oug.orgunitgroupid in (1)
group by ou.organisationunitid,ou2.organisationunitid,ou3.organisationunitid,ou4.organisationunitid,ou5.organisationunitid,ou6.organisationunitid,ou7.organisationunitid,ou8.organisationunitid,ou9.organisationunitid,ou10.organisationunitid,ou11.organisationunitid

Answer 1

你可以尝试一个懒惰的黑客，例如你有:

t=# with c(x,y,z,w) as (values(1,null,3,4),(5,6,7,null))
select *,translate(regexp_replace(c::text,',([^0-9])',',null\1','g'),'()','{}')::int[] from c;
 x | y | z | w |  translate
---+---+---+---+--------------
 1 |   | 3 | 4 | {1,NULL,3,4}
 5 | 6 | 7 |   | {5,6,7,NULL}
(2 rows)

所以只是聚合它们:

t=# with c(x,y,z,w) as (values(1,null,3,4),(5,6,7,null))
,un as (select unnest(translate(regexp_replace(c::text,',([^0-9])',',null\1','g'),'()','{}')::int[]) from c)
select array_agg(unnest) from un;
        array_agg
-------------------------
 {1,NULL,3,4,5,6,7,NULL}
(1 row)

最后使用any:

t=# with c(x,y,z,w) as (values(1,null,3,4),(5,6,7,null))
,un as (select unnest(translate(regexp_replace(c::text,',([^0-9])',',null\1','g'),'()','{}')::int[]) from c)
, ag as (select array_agg(unnest) from un)
select 'there' from ag where 3 = any(array_agg);
 ?column?
----------
 there
(1 row)

t=# with c(x,y,z,w) as (values(1,null,3,4),(5,6,7,null))
,un as (select unnest(translate(regexp_replace(c::text,',([^0-9])',',null\1','g'),'()','{}')::int[]) from c)
, ag as (select array_agg(unnest) from un)
select 'there' from ag where 2 = any(array_agg);
 ?column?
----------
(0 rows)

当然，将元组“转换”为数组是不可靠的，也不能用于产品。您可能需要命名所有字段。但是这样的 monkey hack 可以节省您的打字时间以进行简单的检查