mysql - 查询按组返回攻击力最高的神奇宝贝

Question

我有一个装满口袋妖怪卡片及其攻击的数据库。我想查询以找到每种类型攻击力最强的神奇宝贝。我希望 View 仅显示攻击的名称、类型和损坏。

SELECT p2.MaxD, p2.Type, p1.name 
FROM Pokemon p1 
INNER JOIN ( SELECT type, MAX(damage) MaxD, pokemon_name FROM Attack GROUP BY Type )
p2 ON p1.type = p2.type AND p2.pokemon_name = p1.name

我有这个代码。它返回最高的伤害但不是正确的口袋妖怪。口袋妖怪表没有伤害字段。我正在尝试掌握联接。

结构如下:

攻击表有4个字段:pokemon_name(这次攻击所属的神奇宝贝)、damage、name(攻击的名称)和type(这次攻击所属的神奇宝贝的类型)。

Pokemon 表有 3 个:HP、类型(神奇宝贝的)和名称(神奇宝贝的)。

Answer 1

首先，你必须构建选择，为每种类型选择最大伤害(你已经有了):

SELECT type, MAX(damage) MaxD FROM Attack GROUP BY Type

现在，这不会有好的表现，除非:

• 类型是INT (或 ENUM 或其他数字类型)
• type 上有索引或 type, damage

您不能选择 pokemon_name因为 MySQL 不保证你会得到 pokemon_name匹配MaxD (here's a nice answer 在 stackoverflow 上已经涵盖了这个问题)。

现在您可以选择匹配 pokemon_name 的口袋妖怪了

SELECT p1.pokemon_name, p1.type, p1.damage
FROM Attack p1
INNER JOIN (
    SELECT type, MAX(damage) MaxD FROM Attack GROUP BY Type
) p2 ON p1.type = p2.type
     AND p1.damage = p2.MaxDamage
GROUP BY (p1.type, p1.damage)

最后GROUP BY声明确保拥有多个具有相同攻击伤害的口袋妖怪不会导致一个 type,damage 的多个记录对。

同样，您将通过替换 pokemon_name 获得良好的性能与 pokemon_id .也许你应该谷歌 database normalization 一会儿[wikipedia] , [first tutorial] .您可能还想查看 this Q&A out，它很好地概述了“关系表”的含义。

现在你有正确的pokemon_name (为了您的程序，我希望您将其替换为 pokemon_id )并且您可以将它们放在一起:

SELECT p1.pokemon_name, p1.type, p1.damage, p.*
FROM Attack p1
INNER JOIN (
    SELECT type, MAX(damage) MaxD FROM Attack GROUP BY Type
) p2 ON p1.type = p2.type
     AND p1.damage = p2.MaxDamage
INNER JOIN Pokemon p
     ON  p.pokemon_name = p1.pokemon_name
GROUP BY (p1.type, p1.damage)

---

理想的例子

在完美世界中，您的数据库将如下所示:

-- Table with pokemons
CREATE TABLE `pokemons` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255),
    -- More fields
    PRIMARY KEY (`id`)
)

-- This contains pairs as (1,'Wather'), (2, 'Flame'), ...
CREATE TABLE `AttackTypes` (
    `id`,
    `name` VARCHAR(255)
)

-- Create records like (1, 2, 3, 152)
-- 1 = automatically generated keys
-- 2 = id of pokemon (let say it's Pikachu :P)
-- 3 = type of attack (this say it's Electric)
-- 152 = damage
-- This way each pokemon may have multiple attack types (Charizard flame + wind)
CREATE TABLE `Attacks` (
    `id`,
    `pokemonID` INT NOT NULL, -- Represents pokemons.id
    `typeID` INT NOT NULL, -- Represents attack.id
    `damage` INT
)

ID 字段始终 PRIMARY KEY , NOT NULL和 AUTO_INCREMENT在这个例子中

然后从中选择，再次首先获取类型:

SELECT MAX(attack.damage) AS mDmg, attack.typeID
FROM attack
GROUP BY attack.typeID

比获取口袋妖怪ID:

SELECT a.pokemonID, a.damage, a.typeID
FROM attack AS a
INNER JOIN (
    SELECT MAX(a.damage) AS mDmg, a.typeID
    FROM attack AS a
    GROUP BY a.typeID
) AS maxA
    ON a.typeID = maxA.typeID
        AND a.damage = mDmg
GROUP BY (a.typeID)

一旦您涵盖了所有内容，您实际上可以选择 pokemon 数据

SELECT aMax.pokemonID as id,
    aMax.damage, 
    p.name AS pokemonName,
    aMax.typeID AS attackTypeID,
    t.name AS attackType

FROM (
    SELECT a.pokemonID, a.damage, a.type
    FROM attack AS a
    INNER JOIN (
        SELECT MAX(a.damage) AS mDmg, a.type
        FROM attack AS a
        GROUP BY a.type
    ) AS maxA
        ON a.type = maxA.type
            AND a.damage = mDmg
    GROUP BY (a.type)
) AS aMax

INNER JOIN pokemons AS p
    ON p.id = aMax.pokemonID

INNER JOIN AttackTypes AS t
    ON t.id = aMax.typeID

性能提示:

• 您可以添加字段 MaxDamage进入AttackTypes (这将由存储过程计算)并将为您节省一级 nasted 查询
• 全部ID字段应为 PRIMARY KEY小号
• 索引Attacks.typeID允许您快速获得所有能够进行此类攻击的神奇宝贝
• 索引Attack.damage让你快速找到最强的攻击
• 索引Attack.type, Attack.damage (两个字段)在寻找每次攻击的最大值时会很有帮助
• 索引Attack.pokemonID将查找 pokemon -> attack -> attack type name更快