mysql查询同表select

Question

我有同 table 的人

Name               |       Score    |   Date

Don                |         A      |   12-10-2014              
Don                |         B      |   12-10-2014           
Mary               |         A      |   12-10-2014
Mary               |         C      |   12-10-2014
Mary               |         D      |   12-10-2014
Jack               |         A      |   12-10-2014
Jack               |         B      |   12-10-2014
Jack               |         C      |   12-10-2014

我想获得得分A和B的人的记录。

玛丽 |一个 | 12-10-2014

不会出现在结果中，因为她没有得分 B。结果必须同时具有 A 和 B。

所以输出应该是这样的。玛丽不应该出现在结果中，因为她只有 A 分

Name               |       Score    |   Date

Don                |         A      |   12-10-2014              
Don                |         B      |   12-10-2014          
Jack               |         A      |   12-10-2014
Jack               |         B      |   12-10-2014

我尝试了以下查询，但结果不正确。

 select * 
 from people 
 where score='B' and Name in (select Name 
                              from people 
                              where score='A' 
                              group by Name)

Answer 1

此子查询为您提供至少一个分数为 A 和一个分数为 B 的名称值列表。

SELECT DISTINCT a.Name 
  FROM people AS a
  JOIN people AS b ON a.Name = b.Name AND a.score='A' AND b.score = 'B'

然后，您可以加入子查询来拉取您需要的记录。

SELECT people.Name, people.Score, people.Date
  FROM people
  JOIN (
        SELECT DISTINCT a.Name 
          FROM people AS a
          JOIN people AS b ON a.Name = b.Name AND a.score='A' AND b.score = 'B'
       ) ab ON ab.Name = people.Name
 WHERE Score IN ('A','B')