MySQL查询根据另一列查找匹配列？

Question

抱歉，我不知道如何表达问题的标题。我有一张这样的 table

Prod       Part      Number

0001       101       3
0001       102       2
0001       103       1

0002       101       3
0002       102       2
0002       103       4

0003       101       2
0003       102       3
0003       103       6

0004       101       3
0004       102       2
0004       103       1

我想找到所有零件的每个零件编号都正确的产品。所以类似

SELECT Prod from table 
WHERE     (Number of Part(101)) = 3 
      AND (Number of Part(102)) = 2 
      AND (Number of Part(103)) = 1

输出为:

Prod
0001
0004

我怎样才能实现这一目标？

Answer 1

您可以使用一些内部联接

  select t1.Prod from 
  ( select Prod 
  from my_table 
  where part = 101
  and Number = 3 )  t1
  inner join  
  ( select Prod 
  from my_table 
  where part = 102
  and Number = 2 )  t2 on t1.Prod = t2.Prod
  inner  join 
  ( select Prod 
  from my_table 
  where part = 103
  and Number = 1 ) t3 on t1.Prod = t3.Prod

http://sqlfiddle.com/#!9/014fe4/8