时间戳

首页 > 解决方案 > 如何选择 postgres 多对一关系作为单行？

postgresql - 如何选择 postgres 多对一关系作为单行？

问题描述

我在 Animals 和它们的属性之间有一个多对一的关系。因为不同的动物具有不同的属性，所以我希望能够选择所有具有属性名称的动物作为列标题，并且该动物没有该属性的 NULL 值。

像这样...

TABLE_ANIMALS
ID  | ANIMAL      | DATE          | MORE COLS....
1   | CAT         | 2012-01-10    | ....
2   | DOG         | 2012-01-10    | ....
3   | FROG        | 2012-01-10    | ....
...

TABLE_ATTRIBUTES
ID  | ANIMAL_ID | ATTRIBUE_NAME     | ATTRIBUTE_VALUE
1   | 1         | noise             | meow
2   | 1         | legs              | 4
3   | 1         | has_fur           | TRUE
4   | 2         | noise             | woof
5   | 2         | legs              | 4
6   | 3         | noise             | croak
7   | 3         | legs              | 2
8   | 3         | has_fur           | FALSE
...

QUERY RESULT
ID  | ANIMAL    | NOISE   | LEGS  | HAS_FUR 
1   | CAT       | meow    | 4     | TRUE
2   | DOG       | woof    | 4     | NULL
3   | FROG      | croak   | 2     | FALSE

我该怎么做？重申一下，重要的是所有列都存在，即使一个动物没有该属性，例如本例中的“DOG”和“HAS_FUR”。如果它没有属性，它应该只是空。

标签： postgresqlmany-to-one

解决方案

一个简单的连接、聚合和分组怎么样？

create table table_animals(id int, animal varchar(10), date date);
create table table_attributes(id varchar(10), animal_id int, attribute_name varchar(10), attribute_value varchar(10));

insert into table_animals values (1, 'CAT',  '2012-01-10'),
                                 (2, 'DOG',  '2012-01-10'),
                                 (3, 'FROG',  '2012-01-10');

insert into table_attributes values (1, 1, 'noise', 'meow'),
                                    (2, 1, 'legs', 4),
                                    (3, 1, 'has_fur', TRUE),
                                    (4, 2, 'noise', 'woof'),
                                    (5, 2, 'legs', 4),
                                    (6, 3, 'noise', 'croak'),
                                    (7, 3, 'legs', 2),
                                    (8, 3, 'has_fur', FALSE);

select ta.animal,
       max(attribute_value) filter (where attribute_name = 'noise') as noise,
       max(attribute_value) filter (where attribute_name = 'legs') as legs,
       max(attribute_value) filter (where attribute_name = 'has_fur') as has_fur
from table_animals ta
left join table_attributes tat on tat.animal_id = ta.id
group by ta.animal

这是一个rextester示例

此外，您可以将聚合更改为MAX CASE WHEN...但MAX FILTER WHERE具有更好的性能。

推荐阅读