Mysql改进select联表查询

Question

我正在尝试获得以下行为:

+-----+--------------+-----+-----+-----+-------+-------+-------+--------+--------+
| id  | name         | hp  | atk | def | spatk | spdef | speed |  type1 |  type2 |
+-----+--------------+-----+-----+-----+-------+-------+-------+--------+--------+
|   1 | Bulbasaur    |  45 |  49 |  49 |    65 |    65 |    45 |  GRASS | POISON |
|   2 | Ivysaur      |  60 |  62 |  63 |    80 |    80 |    60 |  GRASS | POISON |
|   3 | Venusaur     |  80 |  82 |  83 |   100 |   100 |    80 |  GRASS | POISON |
+-----+--------------+-----+-----+-----+-------+-------+-------+--------+--------+

其中3个表定义如下:

pokedex: 
+----+------------+----+-----+-----+-------+-------+-------+
| id | name       | hp | atk | def | spatk | spdef | speed |
+----+------------+----+-----+-----+-------+-------+-------+
|  1 | Bulbasaur  | 45 |  49 |  49 |    65 |    65 |    45 |
|  2 | Ivysaur    | 60 |  62 |  63 |    80 |    80 |    60 |
|  3 | Venusaur   | 80 |  82 |  83 |   100 |   100 |    80 |
+----+------------+----+-----+-----+-------+-------+-------+

poke_type:
+------------+---------+
| pokedex_id | type_id |
+------------+---------+
|          1 |      13 |
|          1 |       5 |
|          2 |      13 |
|          2 |       5 |
+------------+---------+

type:
+----+----------+
| id | name     |
+----+----------+
|  1 | NONE     |
|  5 | POISON   |
| 13 | GRASS    |
+----+----------+

众所周知，每个 pokedex 条目都与 poke_type 表中的 2 个 type 相关联。我试图进行如下查询:

USE pokemon;
SELECT  dex.*, 
        t1.name AS type1, 
        t2.name AS type2 
FROM pokedex AS dex, 
    (SELECT pt.pokedex_id AS dexid, t.name AS name 
     FROM pokedex AS d 
          INNER JOIN poke_type AS pt ON d.id = pt.pokedex_id 
          INNER JOIN type as t ON pt.type_id = t.id
    ) AS t1, 
    (SELECT pt.pokedex_id AS dexid, t.name AS name 
     FROM pokedex AS d 
          INNER JOIN poke_type AS pt ON d.id = pt.pokedex_id 
          INNER JOIN type as t ON pt.type_id = t.id
    ) AS t2
WHERE dex.id = t1.dexid AND dex.id = t2.dexid AND t1.name <> t2.name
GROUP BY dex.id -- remove duplicates

这是一个丑陋的查询，可能效率不高。关于如何以不同方式改进此查询/选择的任何想法？

Answer 1

如果哪种类型是 type1 哪种类型是 type2 对您来说并不重要，这可能是您可以获得的最简单的方法:

SELECT  
    dex.*, 
    MAX(t.name) AS type1, 
    MIN(t.name) AS type2 
FROM 
    pokedex AS dex 
    JOIN poke_type AS pt ON dex.id = pt.pokedex_id 
    JOIN type as t ON pt.type_id = t.id
GROUP BY dex.id

sqlfiddle