I have this database diagram:
我有这个数据库图:
I was trying to create a query that performs some joins between these tables and result in something like this:
我试图创建一个查询,在这些表之间执行一些连接,结果是这样的:
||name_|| ||Type # 1|| ||Type #2|| ||Ability #1|| ||Ability #2||
(the last four columns represent the types' and abilities' names, no their ID. Also, type #2 and/or ability #2 may be null depending on the pokémon)
(最后四列代表类型和能力的名称,没有他们的ID。另外,类型#2和/或能力#2可能为空,具体取决于神奇宝贝)
I am totally stuck. How should I do this?
我完全陷入困境。我该怎么做?
2 个解决方案
#1
3
You are going to want to start by joining the tables:
您将要加入表格开始:
select k.name_,
t.type,
a.ability
from kanto k
left join kantotype kt
on k.pokemonid = kt.pokemonid
left join types t
on kt.typeid = t.typeid
left join kantoability ka
on k.pokemonid = ka.pokemonid
left join abilities a
on ka.abilityid = a.abilityid
If you need help learning joining syntax, here is a great visual explanation of joins.
如果您需要帮助学习连接语法,这里是一个很好的连接的视觉解释。
This will give you a list of all of the names, and their type and ability.
这将为您提供所有名称及其类型和能力的列表。
If you want to rotate the values that you get into columns, then you can use an aggregate function with a CASE expression to pivot the data:
如果要将所获得的值旋转到列中,则可以使用带有CASE表达式的聚合函数来旋转数据:
select k.name_,
max(case when t.typeid = 1 then t.type end) Type1,
max(case when t.typeid = 2 then t.type end) Type2,
max(case when a.abilityid = 1 then a.ability end) Ability1,
max(case when a.abilityid = 2 then a.ability end) Ability2
from kanto k
left join kantotype kt
on k.pokemonid = kt.pokemonid
left join types t
on kt.typeid = t.typeid
left join kantoability ka
on k.pokemonid = ka.pokemonid
left join abilities a
on ka.abilityid = a.abilityid
group by k.name_
In each one of the case expressions you will want to identify the id to transform:
在每个case表达式中,您将要标识要转换的id:
max(case when t.typeid = 1 then t.type end)
^-- replace with your actual value
#2
0
You may want to change your approach and have your database query return something like:
您可能希望更改方法并让数据库查询返回如下内容:
||name_|| ||Type|| ||Ability||
instead of
||name_|| ||Type # 1|| ||Type #2|| ||Ability #1|| ||Ability #2||
In other words, return types and abilities as records (rows) instead of columns. For this, see bluefeet query.
换句话说,返回类型和能力作为记录(行)而不是列。为此,请参阅bluefeet查询。
#1
3
You are going to want to start by joining the tables:
您将要加入表格开始:
select k.name_,
t.type,
a.ability
from kanto k
left join kantotype kt
on k.pokemonid = kt.pokemonid
left join types t
on kt.typeid = t.typeid
left join kantoability ka
on k.pokemonid = ka.pokemonid
left join abilities a
on ka.abilityid = a.abilityid
If you need help learning joining syntax, here is a great visual explanation of joins.
如果您需要帮助学习连接语法,这里是一个很好的连接的视觉解释。
This will give you a list of all of the names, and their type and ability.
这将为您提供所有名称及其类型和能力的列表。
If you want to rotate the values that you get into columns, then you can use an aggregate function with a CASE expression to pivot the data:
如果要将所获得的值旋转到列中,则可以使用带有CASE表达式的聚合函数来旋转数据:
select k.name_,
max(case when t.typeid = 1 then t.type end) Type1,
max(case when t.typeid = 2 then t.type end) Type2,
max(case when a.abilityid = 1 then a.ability end) Ability1,
max(case when a.abilityid = 2 then a.ability end) Ability2
from kanto k
left join kantotype kt
on k.pokemonid = kt.pokemonid
left join types t
on kt.typeid = t.typeid
left join kantoability ka
on k.pokemonid = ka.pokemonid
left join abilities a
on ka.abilityid = a.abilityid
group by k.name_
In each one of the case expressions you will want to identify the id to transform:
在每个case表达式中,您将要标识要转换的id:
max(case when t.typeid = 1 then t.type end)
^-- replace with your actual value
#2
0
You may want to change your approach and have your database query return something like:
您可能希望更改方法并让数据库查询返回如下内容:
||name_|| ||Type|| ||Ability||
instead of
||name_|| ||Type # 1|| ||Type #2|| ||Ability #1|| ||Ability #2||
In other words, return types and abilities as records (rows) instead of columns. For this, see bluefeet query.
换句话说,返回类型和能力作为记录(行)而不是列。为此,请参阅bluefeet查询。