MySQL:根据多行的值选择数据

Question

我正在尝试编写一个 SQL 语句，从一个表中选择数据，其中一个人在同一列中有多个值。例如，基于下表:我需要一个查询来选择“玩具”列中同时拥有球棒和棒球的所有个人的所有行。

------------------------
| ID | Name | Toy      |
------------------------
|  1 | Jack | Bat      |
|  2 | Jim  | Baseball |
|  3 | Jack | Baseball |
|  4 | John | Bat      |
|  5 | Jim  | Football |
|  6 | Jack | Glove    |
------------------------

我希望结果是这样的:

-------------------
| Name | Toy      |
-------------------
| Jack | Bat      |
| Jack | Baseball |
-------------------

我希望这是有道理的。谢谢。

Answer 1

select distinct t.name, t.toy
from your_table t
where name in 
(
  select name
  from your_table
  where toy in ('bat','baseball') 
  group by name
  having count(distinct toy) = 2
)
and toy in ('bat','baseball') 

如果你只需要名字你可以做

  select name
  from your_table
  where toy in ('bat','baseball') 
  group by name
  having count(distinct toy) = 2

SQLFiddle demo