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');

但是怎么插入array_agg里面unnest呢？

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;

我想收到喜欢的东西

这就是我收到的

标签： sqlpostgresql

Answer 1

试试这个（先unnest然后再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