时间戳

首页 > 解决方案 > 在 MySQL 中加入多个表

mysql - 在 MySQL 中加入多个表

问题描述

我正在开发一个关于神奇宝贝角色的网络应用程序，它应该显示以下数据：名称、图像、类别、类型、技能（最多 3 个）和弱点（最多 3 个）。

我正在处理的模型和表格如下：

关系模型

关于表格的注意事项： Type=Tipo、Category=Categoria、Weaknesses=Debilidad、Skill=Habilidad、PokemonType=PokemonTipo、PokemonWeaknesses=PokemonDebilidad、PokemonSkill=PokemonHabilidad

“PokemonTipo”、“PokemonDebilidad”和“PokemonHabilidad”表保存了每个 Pokemon 的类型、弱点和能力的“多对多”记录。

我正在使用的选择是这个。

SELECT po.idPokemon, po.nombre AS 'Pokemon', po.imagen, cat.nombre AS 'Categoria', ti.nombre 
AS 'Tipo',
ha.nombre AS 'Habilidad', de.nombre AS 'Debilidad'
FROM Pokemon po 
INNER JOIN Categoria cat
ON po.idCategoria = cat.idCategoria
INNER JOIN PokemonTipo pt
ON po.idPokemon = pt.idPokemon
INNER JOIN Tipo ti
ON ti.idTipo = pt.idTipo
INNER JOIN PokemonHabilidad ph
ON po.idPokemon = ph.idPokemon
INNER JOIN Habilidad ha
ON ha.idHabilidad = ph.idHabilidad
INNER JOIN PokemonDebilidad pd
ON po.idPokemon = pd.idPokemon
INNER JOIN Debilidad de
ON de.idDebilidad = pd.idDebilidad
GROUP BY po.idPokemon
ORDER BY po.idPokemon

从图中可以看出，使用我使用的 Select，我只为每个 Pokémon 获得一项技能 (Habilidad) 和一项弱点 (Debilidad)。

加入

我一直没能解决的问题是：如何在同一个Select的不同列中，获取并分离出每个宝可梦的技能（Habilidad）和弱点（Debilidad）。

对于每个神奇宝贝，我必须具有以下字段：

名称、图像、类别、类型、技能 1、技能 2、技能 3、弱点 1、弱点 2、弱点 3

如果您想进行测试，可以在此链接中获得数据库脚本。https://github.com/jcesarux/pokebase/blob/master/pokebase.sql

对于如何解决这个问题，我将不胜感激。

问候并感谢您的支持。！

标签： mysqljoinselect

解决方案

您的查询不是一个有效的聚合查询，因为selectandgroup by子句不一致。不幸的是，与大多数其他数据库不同，MySQL 可以愉快地编译和运行它，实际上每个 pokemon 只获得一种（任何）技能和弱点。

row_number()您可以使用条件聚合来做您想做的事情。这个想法是在子查询中对每个口袋妖怪的技能和周数进行排名，然后在外部查询中旋转结果集。

SELECT po.idPokemon, 
    po.nombre AS Pokemon, 
    po.imagen, 
    cat.nombre AS Categoria, 
    ti.nombre AS Tipo,
    MAX(CASE WHEN ha.rn = 1 THEN ha.nombre END) AS Habilidad1, 
    MAX(CASE WHEN ha.rn = 2 THEN ha.nombre END) AS Habilidad2, 
    MAX(CASE WHEN ha.rn = 3 THEN ha.nombre END) AS Habilidad3, 
    MAX(CASE WHEN de.rn = 1 THEN de.nombre END) AS Debilidad1,
    MAX(CASE WHEN de.rn = 2 THEN de.nombre END) AS Debilidad2,
    MAX(CASE WHEN de.rn = 3 THEN de.nombre END) AS Debilidad3
FROM Pokemon po 
INNER JOIN Categoria cat ON po.idCategoria = cat.idCategoria
INNER JOIN PokemonTipo pt ON po.idPokemon = pt.idPokemon
INNER JOIN Tipo ti ON ti.idTipo = pt.idTipo
INNER JOIN (
    SELECT ph.idPokemon, ha.*, ROW_NUMBER() OVER(PARTITION BY ph.idPokemon ORDER BY ph.idHabilidad) rn
    FROM PokemonHabilidad ph 
    INNER JOIN Habilidad ha ON ha.idHabilidad = ph.idHabilidad
) ha ON ha.idPokemon = po.idPokemon
INNER JOIN (
    SELECT pd.idPokemon, de.*, ROW_NUMBER() OVER(PARTITION BY pd.idPokemon ORDER BY de.idHabilidad) rn
    FROM PokemonDebilidad pd 
    INNER JOIN Debilidad de ON de.idDebilidad = pd.idDebilidad
) de ON ON de.idPokemon = po.idPokemon
GROUP BY po.idPokemon, cat.idCategoria, ti.idTipo
ORDER BY po.idPokemon

请注意，窗口函数仅在 MySQL 8.0 中可用。

推荐阅读