代码之家 › 专栏 › 技术社区 › khex

如何对数组进行UNNEST、GROUP和JOIN?

postgresql sql

khex · 技术社区 · 7 年前

不需要 cars.colors 阵列,那么集团或者将数组中的所有颜色分组,然后加入它。但它是如此的安静,它是嵌套的选择

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;

这就是我得到的

0 回复 | 直到 7 年前

Gen Wan Staidly 7 年前

请尝试以下操作(先取消Nest,然后再执行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

Chris McGraw 7 年前

你有没有试过用with语句把它撕成碎片?

with cars_qry as (
select cars_id, name, unnest(colors) as shade
from cars
)

select c.name, array_agg(t.shade) as shade_colors
from cars_qry as c
left join tags as t
   on c.cars_id = t.tags_id
group by c.name

from (select cars_id, name, unnest(colors) as shade from cars) as c

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

1 年前

Kallydi · jdbc无法连接到docker中的postgresql数据库

1 年前

dietzi96 · Pandas DataFrame.to_sql随机和静默地失败,没有错误消息

1 年前

Peter Schofield · 类型转换Postgresql

1 年前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

1 年前

Kevin Smeeks · Pyspark JDBC分区读取

1 年前

Micah Armantrout · 如何使用sql脚本将数据插入到代码优先的数据库中?

1 年前

Vanortton · 使用Docker开始应用程序开发还是在开发过程中配置Docker?[关闭]

1 年前

Andrus · 如何在sql中查找第二个匹配项

1 年前

MWood · Postgres jsonpath用于引用单例元素或元素数组

1 年前