Mysql：找到多个孩子的父母

I have 2 tables: players and items. Now I'm looking for player who has item with known properties ex.

我有2张桌子:球员和物品。现在我正在寻找具有已知属性ex的项目的玩家。

SELECT players.`name` FROM `players`
INNER JOIN `items` ON players.`id`=items.`ownerId` 
WHERE items.`itemType` = 1 AND items.`itemClass` = 2 AND items.`itemColor` = 3

How i can find player which has more than one item i want? It is even possible in one query?

我怎么能找到有我想要的多个项目的玩家?甚至可以在一个查询中?

Ex. i wanna find player which has both items : type=1 class=2 color=3 , type=2 class=3 color=4

防爆。我想找到兼具两个项目的玩家:type = 1 class = 2 color = 3,type = 2 class = 3 color = 4

I have an idea how to do it in multiple querys: just add players.id IN (...) on every next query.

我知道如何在多个查询中执行此操作:只需在每个下一个查询中添加players.id IN(...)。

Thanks for all your help!

感谢你的帮助!

1 个解决方案

#1

The best way to do this is with aggregation.

最好的方法是使用聚合。

select i.ownerId
from Items i
group by i.ownerId
having max(case when i.itemType = 1 and i.itemClass = 2 and i.itemColor = 3
                then 1 else 0
           end) = 1 and
       max(case when i.itemType = 2 and i.itemClass = 3 and i.itemColor = 4
                then 1 else 0
           end) = 1

If you want other information about the owner, you need to join in the players table.

如果您需要有关所有者的其他信息,您需要加入玩家表。

In MySQL, you can simplify the having clause to:

在MySQL中,您可以将having子句简化为:

having max(i.itemType = 1 and i.itemClass = 2 and i.itemColor = 3) = 1 and
       max(i.itemType = 2 and i.itemClass = 3 and i.itemColor = 4) = 1

#1