sql - 如何 UNNEST、GROUP 和 JOIN 数组？

Question

我正在尝试UNNEST cars.colors 数组，然后GROUP 或将数组中的所有颜色分组，然后加入它。但是嵌套的 SELECT 太麻烦了。

这是 SQLFiddle举例说明。

create table cars (
    cars_id integer,
    name    char(32),
    colors  int []
);

insert into cars (cars_id, name, colors) values
    (1, 'liftback',  array [1, 2, 3]),
    (2, 'hatchback', array [2, 4, 6]),
    (3, 'sedan',     array [3, 4, 6]),
    (4, 'phaeton',   array [4, 5, 6]);

create table tags (
    tags_id  integer,
    shade    char(16)
);

insert into tags (tags_id, shade) values
    (1, 'green'), (2, 'blue'),  (3, 'yellow'), (4, 'black'), (5, 'white'), (6, 'red');

但是如何在 unnest 中插入 array_agg 呢？

SELECT
    cars.name,
    array_agg(tags.shade) AS shade_colors
FROM cars
LEFT JOIN tags ON cars.cars_id = tags.tags_id
GROUP BY cars.cars_id;

我想收到类似的东西

这就是我收到的

Answer 1

试试这个(首先取消嵌套，然后再次 array_agg):

select a.name, array_agg(t.shade ) as shade_colors
from (
select c.cars_id, c.name, unnest(c.colors) as colorid
from cars c)a
left join tags t
on a.colorid = t.tags_id
group by a.name

测试结果: SQL<>Fiddle