mysql - SQL 查询仅从另一个表中选择具有一组独占相关实体的结果

Question

我有一个包含 2 个表的关系数据库，比方说 paintings 和 colors。它们通过数据透视表 color_painting 处于多对多关系。

“绘画”表:

id  name
---------------------
1   Lion and cubs
2   Sunset in Ontario

“颜色”表:

id  color
---------------------
1   grey
2   yellow
3   orange

“color_painting”表:

painting_id  color_id
---------------------
1            1
1            2
2            1
2            2
2            3

因此，绘画与颜色的关联如下:

“Lion and cubs”链接到颜色“grey”和“yellow”

“安大略省的日落”链接到“grey”、“yellow”和“orange”

如何编写一个 SQL 查询来选择其中只有特定颜色而没有其他颜色的绘画？

例如我想要只有“灰色”和“黄色”的画。因此，查询应该返回“Lion and cubs”绘画而不是“Sunset”，因为“Sunset”也有“orange”。

是否可以一次查询？ (SQL 应该在 MySQL、SQLite、PostgreSQL 和 SQL Server 方言上运行)。

Answer 1

使用NOT IN 或NOT EXISTS 来排除不同颜色的绘画，其余绘画必须有 2 行以符合您的需要。

select p.id, p.name
from paintings p
join color_painting cp on p.id = cp.painting_id  
where p.id not in
    (
       select painting_id 
       from color_painting cp 
       join colors c on cp.color_id = c.id and c.color not in ('yellow','grey')
    )
group by p.id, p.name
having count(*) = 2