mysql - SELECT 中的 GROUP BY 和 SELECT

Question

我的问题已解决，但现在我有一些性能问题。

我想要一个关于所有版本的我抓到的神奇宝贝的概览，它在我尝试过的范围内有效，但我认为这不是最佳做法。

select 
  #p.idpokemons as ID,
  #p.name as Pokemon,
  #t.name as Trainer,
  tpe.idpokemons as ID,
  (select name from pokemons p where p.idpokemons = tpe.idpokemons) as Pokemon,
  (select name from trainer t where t.idtrainer = tpe.idtrainer) as Trainer,
  max(case when ideditions = 'x' then status end) as statusX, 
  max(case when ideditions = 'y' then status end) as statusY, 
  max(case when ideditions = 'or' then status end) as statusOR, 
  max(case when ideditions = 'as' then status end) as statusAS, 
  max(case when ideditions = 'sun' then status end) as statusSun, 
  max(case when ideditions = 'moon' then status end) as statusMoon 
from trainer_pokemon_edition tpe
#JOIN pokemons p ON p.idpokemons = tpe.idpokemons
#JOIN trainer t ON t.idtrainer = tpe.idtrainer
group by tpe.idpokemons;

编辑:只是将查询更改为类似 Thorstens 的答案，因为它比我的旧答案快。我评论了连接，因为在我的应用程序中我返回了 721 行并且连接查询比子查询慢 0,021s/0,00034s JOINs against 0,0030s/0,013s 子查询持续时间/获取。为什么会这样，Joins 上的 fetchtime 更快，但 duration 却没有？

image of db design

这里是一个链接 sqlfiddle , 带有测试数据。

Answer 1

您想显示您所有的宠物小 Sprite 及其状态吗？但是每个口袋妖怪都有很多 trainer_pokemon_edition 记录，因此每个 x、每个 y 等可能有不同的状态。您必须决定显示哪一个；最大值？显示所有状态的串联字符串？还有什么？同样奇怪的是，有一个表格包含版本，但您的查询处理某些版本并将它们显示在列中。

但是，要获取每个口袋妖怪的状态，只需汇总您的数据:

select 
  (select name from pokemons p where p.idpokemons = tpe.idpokemons) as pokemon,
  max(case when ideditions = 'x' then status end) as statusx, 
  max(case when ideditions = 'y' then status end) as statusy, 
  max(case when ideditions = 'or' then status end) as statusor, 
  max(case when ideditions = 'as' then status end) as statusas, 
  max(case when ideditions = 'sun' then status end) as statussun, 
  max(case when ideditions = 'moon' then status end) as statusmoon, 
  max(case when ideditions = 'b' then status end) as statusb, 
  max(case when ideditions = 'r' then status end) as statusr 
from trainer_pokemon_edition tpe
group by idpokemons;

当然你也可以加入 pokemons 表，而不是在 select 子句中的子查询中获取名称。