sql - 查找未订购所有 3 种比萨饼的顾客

Question

customers:
+-----------+-----------+
| cid       | name      |
+-----------+-----------+
| 1         | a         | 
| 2         | b         |
| 3         | c         |
+-----------+-----------+

pizza:
+-----------+-----------+
| pid       | type      |
+-----------+-----------+
| 1         | sausage   |
| 2         | cheese    |
| 3         | veggies   |
| 4         | sausage   |
| 5         | veggies   |
| 6         | sausage   |
| 7         | sausage   |
+-----------+-----------+

orders:
+-----------+-----------+-----------+
| oid       | cid       | pid       |
+-----------+-----------+-----------+
| 1         | 1         | 1         | 
| 2         | 1         | 2         |
| 3         | 2         | 3         |
| 4         | 3         | 4         |
| 5         | 1         | 5         |
| 6         | 3         | 6         |
| 7         | 3         | 7         |
+-----------+-----------+-----------+

我在理解 sql 逻辑时遇到了一些麻烦。我如何找到没有订购所有 3 种披萨的顾客？这三种类型是香肠、奶酪和蔬菜。我需要使用 NOT EXIST 吗？

Answer 1

您可以使用 having 子句来查找订购少于 3 种披萨的客户:

select  c.name
from    customers c
join    orders c
on      c.cid = c.id
join    pizza p
on      p.id = c.pid
group by
        c.name
having
        count(distinct p.type) < 3