查询返回攻击率最高的Pokemon分组

Question

我有一个数据库充满了精灵宝可梦卡，和他们的攻击。我想做一个查询，根据每种类型找到攻击力最强的Pokemon。我希望视图只显示攻击的名称、类型和损害。

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

我有这个代码。它返回最高的伤害，但不是正确的口袋妖怪。精灵宝可梦桌面上没有伤害字段。我正在尝试掌握joins。

结构是这样的：

攻击表有4个字段: pokemon_name (本次攻击所属的精灵)、damage、name (攻击名称)、type (本次攻击所属的精灵类型)。

Pokemon表有3个: HP、类型( pokemon)和名称( pokemon)。

Answer 1

我不太确定您的模式，但我假设您的attack表的pokemon_name实际上是pokemon的名称。

SELECT  a.*, c.*
FROM    Attack a
        INNER JOIN
        (
            SELECT  type, MAX(damage) MaxD
            FROM Attack 
            GROUP BY Type 
        ) b ON a.Type = b.Type AND
                a.damage = b.MaxD
        INNER JOIN Pokemon c
            ON c.Name = a.pokemon_name AND
                c.Type = a.Type

上面的查询显示了attack表和pokemon表中的所有字段，但如果您确实对name、damage和type感兴趣，则只对attack表执行查询

SELECT  a.*
FROM    Attack a
        INNER JOIN
        (
            SELECT  type, MAX(damage) MaxD
            FROM Attack 
            GROUP BY Type 
        ) b ON a.Type = b.Type AND
                a.damage = b.MaxD

Answer 2

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

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

现在，这不会有很好的性能，除非：

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

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

现在你可以用匹配的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不会导致一个pokemons对的多个记录。

同样，通过用pokemon_id替换pokemon_name，您将获得良好的性能。也许你应该用谷歌搜索database normalization一段时间，[wikipedia]，[first tutorial]。你可能还想看看this Q&A，它很好地概述了“关系表”是什么意思。

现在您有了正确的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字段为ALWAYS PRIMARY KEY__、NOT NULL和AUTO_INCREMENT

以及select from它，再次首先获取类型：

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 data了

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 (将通过存储过程计算)中，并将为您节省一级最新查询
• all ID field max be PRIMARY KEYs
• index on Attacks.typeID允许您快速获取所有支持攻击类型的pokemons

D50上的索引允许您快速查找最强攻击H251 H152 D53(两个字段)上的索引在查找每次攻击的最大值时将很有帮助