时间戳

首页 > 解决方案 > 使用基于子查询的列的 SELECT 语句 (postgres)

sql - 使用基于子查询的列的 SELECT 语句 (postgres)

问题描述

根据子查询的结果（使用 Postgres 9.3）选择列的最优雅（即可读且易于维护）的方法是什么？

以下是一些示例数据：

create temporary table if not exists food (
id serial primary key,
pet_id varchar,
steak integer,
chicken integer,
fish integer,
veg integer
);

insert into food (pet_id, steak, chicken, fish, veg) values
(1, 100, 10, 1, 78),
(2, 140, 100, 1100, 7),
(3, 10, 10, 10, 7);

create temporary table if not exists foodtypes (
id serial primary key,
name varchar,
meat boolean
);

insert into foodtypes (name, meat) values
('steak', true),
('chicken', true),
('fish', true),
('veg', false);

我希望能够为数据库中的所有宠物生成一些输出，其中包含所有多肉的列。目前，我正在使用：

select id, pet_id, steak, chicken, fish from food;

    id  pet_id  steak   chicken fish
0   1   1       100     10      1
1   2   2       140     100     1100
2   3   3       10       10     10
3   7   1       100     10      1
4   8   2       140     100     1100
5   9   3       10       10     10

但是，如果另一家宠物店经营者在foodtypes餐桌上添加了一种新的肉类食品类型会怎样？它不会包含在结果中

理想情况下，为了可维护性和一致性，我希望能够基于子查询选择我的列 - 例如：

select name, (select distinct name from foodtypes where meat = true) from food;

这行不通。我读过横向连接可能很有用，但我不确定它如何帮助实现这一目标。

标签： sqlpostgresqljoin

解决方案

我认为处理这个最合适的方法是改变food表格的格式

create table food (
id serial primary key,
pet_id varchar,
foodtype varchar, 
amount int
);

这样，您就可以对列进行常规food.foodtype连接foodtypes.name。这将在未来证明您的查询，因为任何新的食物类型都会被自动处理。

推荐的表设置

create table food (
id serial primary key,
pet_id varchar,
foodtype varchar, 
amount int
);

insert into food (pet_id, foodtype, amount) values
(1, 'steak', 100),
(1, 'chicken', 10),
(1, 'fish', 1),
(1, 'veg', 78),
(2, 'steak', 140),
(2, 'chicken', 100),
(2, 'fish', 1100),
(2, 'veg', 7),
(3, 'steak', 10),
(3, 'chicken', 10),
(3, 'fish', 10),
(3, 'veg', 7);

create table foodtypes (
id serial primary key,
name varchar,
meat boolean
);

insert into foodtypes (name, meat) values
('steak', true),
('chicken', true),
('fish', true),
('veg', false);

推荐查询

select * 
from food f 
join foodtypes ft on 
    f.foodtype = ft.name
where ft.meat = true

您可以在此处试用推荐设置的示例。

推荐阅读