sql - array_agg 来自两个不同的表，没有连接

Question

我需要将输入作为来自两个不相关的不同表的数组。

样本数据

CITY1 表

姓名	TOT_POP
城市1	10
城市2	20

设施表

姓名	配额
f1	1
f2	2
f3	3
f4	4

关闭我找到的解决方案

SELECT ARRAY_AGG(t1."TOT_POP") as City_Pop, ARRAY_AGG(t2."Quota") as Facility_Quota FROM 
(SELECT "TOT_POP", row_number() OVER (order by (SELECT 0)) FROM CITY1) as t1 right JOIN  
(select "Quota", row_number() OVER (order by (SELECT 0)) FROM FACILITIES) as t2 on t1.row_number = t2.row_number; 

输出数组包含 null 并且在我的情况下不需要加入键。

City_Pop	Facility_Quota
{10,20,NULL,NULL}	{1,2,3,4}

我想要以下结果，如果可能的话不使用 join

City_Pop	Facility_Quota
{10,20}	{1,2,3,4}

标签： sqlpostgresql

Answer 1

你把事情复杂化了。只需使用两个标量子查询：

select (select array_agg(tot_pop) from city1) as city_pop,
       (select array_agg(quota) from facilities) as facility_quot;

或稍快：

select (array(select tot_pop from city1)) as city_pop,
       (array(select quota from facilities)) as facility_quot;