在 postgresql 中将具有不同行的多个子查询输出检索到单个响应中

Question

我想将几个子查询(每个子查询返回具有不同行数的单个列(的输出接收到单个响应中。

我正在尝试以下内容，但它无法正常工作，因为[21000] ERROR: more than one row returned by a subquery used as an expression错误。

SELECT
  (SELECT DISTINCT org_name
   FROM table_1
   WHERE column1= 1)                        AS terminal_1,
  (SELECT org_name
   FROM table_5
   WHERE column5= 1)                          AS cfs_1,
  (SELECT DISTINCT org_name
   FROM table_4 where column7= 136 )     AS terminals_2,
  (SELECT org_name
   FROM table_6
   WHERE column1=7)                           AS cfs_2;

我想这样做，以便将来我可以添加更多子查询，每个子查询还将返回具有不同行数的单个列。有没有一个好的做法来实现同样的目标？谢谢。

编辑1：输出响应可以是任何逻辑形式，即表格或JSON。

Answer 1

让我们成为PostgreSQL的优秀数组怎么样？

with terminal_1 as (
  SELECT array_agg (DISTINCT org_name) as term1_name
  FROM table_1
  WHERE column1= 1
),
cfs_1 as (
  SELECT array_agg (org_name) as cfs1_name
  FROM table_5
  WHERE column5= 1
),
terminals_2 as (
  SELECT array_agg (DISTINCT org_name) as term2_name
  FROM table_4 where column7= 136
),
cfs_2 as (
  SELECT array_agg (org_name) as cfs2_name
  FROM table_6
  WHERE column1=7
)
select
  term1_name, cfs1_name, term2_name, cfs2_name
from
  terminal_1
  cross join cfs_1
  cross join terminals_2
  cross join cfs_2

虽然这不会执行您想要的化妆品，但我相信它确实以您想要的方式提供内容。

Answer 2

我相信你只需要UNION ALL

SELECT DISTINCT  't1' tableNum, org_name
FROM table_1
WHERE column1= 1
UNION ALL
SELECT 't5' tableNum, org_name
FROM table_5
WHERE column5= 1
UNION ALL
SELECT DISTINCT 't4' tableNum, org_name
FROM table_4 
where column7= 136
UNION ALL
SELECT 't6' tableNum, org_name
FROM table_6
WHERE column1=7