MYSQL分组和筛选筛选器

Question

所以我有一个衣柜桌子，里面有拥有彩色衬衫的人的记录。就像这样，

================================
| People   |    Shirt          |
================================
| Peter    |    red            |
| Peter    |    green          |
| Peter    |    blue           |
| Peter    |    blue           |
| John     |    red            |
| John     |    green          |
| Kenny    |    yellow         |
| Kenny    |    yellow         |
| Kenny    |    green          |
| Sam      |    yellow         |
| Sam      |    green          |
| Sam      |    purple         |
| Chris    |    red            |
| Chris    |    red            |
================================

我需要帮助查询一个人有多少衬衫，只有红色衬衫。下面的查询很接近，但不是我想要的。

Select people, count(shirt) from Wardrobe where shirt='red' group by people.

结果是

===========================
| People   | count(shirt) |
===========================
| Peter    |    1         |
| John     |    1         |
| Chris    |    2         |
==========================

我想要从查询中得到的结果会是这样的

===========================
| People   | count(shirt) |
===========================
| Chris    |    2         |
==========================

+++++++++++++++++++++++++++++++++++++++

更新

如果我碰巧连接多个表，并且总记录数以百万计，那么哪种技术会更好、更有效？

Answer 1

获取一个人拥有的红色衬衫的总数，将其与衬衫的总数进行比较，因此您只返回这些相同的行。

SELECT People, SUM(Shirt = "red") AS redcount
FROM Wardrobe
GROUP BY People
HAVING redcount = COUNT(*)

或者干脆把穿非红色衬衫的人排除在外：

SELECT People, COUNT(*) AS redcount
FROM Wardrobe
WHERE People NOT IN (
    SELECT People
    FROM Wardrobe
    WHERE Shirt != "red")
GROUP BY People

Answer 2

更长的版本:)

select w.people, w.cnt
from (
Select people,count(shirt) as cnt
from Wardrobe 
where shirt = 'red'
group by people) w
join (
  select people, count(shirt) as d_cnt 
  from Wardrobe 
  group by people) t
on w.people = t.people  and w.cnt = t.d_cnt

Answer 3

您必须使用子查询。

选择人员，计数(*)作为总数(从衣柜中选择DISTINCT * FROM WHERE =‘red’按人分组)；