使用来自其他表的计数(*)结果的SQL生成表
使用来自其他表的计数(*)结果的SQL生成表
提问于 2018-11-07 16:02:42
我正在构建一个口袋妖怪数据集，并希望在它上运行一些查询。这是数据库的设置：
create table pokedex(
name varchar(20) not null,
weigth int not null,
height int not null,
primary key(name)
);
create table trainer(
name varchar(20) not null,
location varchar(20) not null,
gender varchar(10) not null,
birth_year int not null,
primary key(name)
);
create table trainer_pokemon(
trainer_name varchar(20) not null,
pokemon_name varchar(20) not null,
level int not null,
year_obtained int not null,
primary key(trainer_name, pokemon_name, level, year_obtained),
foreign key(trainer_name) references trainer(name),
foreign key(pokemon_name) references pokedex(name)
);
create table type(
name varchar(20) not null,
primary key(name)
);
create table poke_type(
pokemon_name varchar(20) not null,
type_name varchar(20) not null,
primary key(pokemon_name, type_name),
foreign key(pokemon_name) references pokedex(name),
foreign key(type_name) references type(name)
);这样做的想法是，数据集不应该有多余的数据，所以如果我想为每个培训师获得一个包含最常用的Pokemons类型的表，我需要为每种类型获得一张表，或者至少这就是我所相信的atm：
with psychics as (
select trainer_name, count(type_name) psychic from trainer_pokemon as tp
inner join poke_type as pt on pt.pokemon_name = tp.pokemon_name
group by tp.trainer_name, pt.type_name
having pt.type_name = 'Psychic'
),
waters as (
select trainer_name, count(type_name) water from trainer_pokemon as tp
inner join poke_type as pt on pt.pokemon_name = tp.pokemon_name
group by tp.trainer_name, pt.type_name
having pt.type_name = 'Water'
),
select tp.trainer_name, w.water, p.psychic from trainer_pokemon as tp
inner join waters as w on w.trainer_name = tp.trainer_name
inner join psychics as p on p.trainer_name = tp.trainer_name
group by tp.trainer_name, w.water, p.psychic但是，这不会导致没有特定类型的口袋妖怪(本例中为水/灵媒)的培训人员。
有谁能为我指出正确的方向，在他们的口袋妖怪集合中建立一个特定类型的训练器表吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-11-07 17:19:31
假设您使用的是一个相对更新的Postgresql版本(您已经标记了Mysql和Postgres，它们是非常不同的数据库)
SELECT tp.trainer_name,
COUNT(*) FILTER (WHERE pt.type_name = 'Psychic') as psychic,
COUNT(*) FILTER (WHERE pt.type_name = 'Water') as water
FROM trainer_pokemon tp, poke_type pt
WHERE tp.pokemon_name = pt.pokemon_name
GROUP BY 1在您的问题中发布的查询可能会作为表的多个扫描来执行，但是如果您确实想要这样做，则需要使用“左外部联接”(外层关键字是可选的) do：
with psychics as (
select trainer_name, count(type_name) AS psychic
from trainer_pokemon as tp
inner join poke_type as pt on pt.pokemon_name = tp.pokemon_name
group by tp.trainer_name, pt.type_name
having pt.type_name = 'Psychic'
), waters as (
select trainer_name, count(type_name) AS water
from trainer_pokemon as tp
inner join poke_type as pt on pt.pokemon_name = tp.pokemon_name
group by tp.trainer_name, pt.type_name
having pt.type_name = 'Water'
)
select tp.trainer_name, w.water, p.psychic
from trainer_pokemon as tp
left join waters as w on w.trainer_name = tp.trainer_name
left join psychics as p on p.trainer_name = tp.trainer_name或者另一种方法，如果你有两种以上的类型，你的比例可能会更高：
with trained as (
select trainer_name, pt.type_name, count(*) as num
from trainer_pokemon as tp
inner join poke_type as pt on pt.pokemon_name = tp.pokemon_name
group by tp.trainer_name, pt.type_name
)
select tp.trainer_name, w.num as water, p.num as psychic
from trainer_pokemon as tp
left join trained as w on tp.trainer_name = w.trainer_name and w.type_name = 'Water'
left join trained as p on tp.trainer_name = p.trainer_name and p.type_name = 'Psychic'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53193235
复制相关文章
相似问题
腾讯云开发者
