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

Question

我有一个有两个表的关系数据库，比方说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“

"Sunset in Ontario“链接到"grey”、"yellow“和"orange”

如何编写SQL查询，以选择只有特定颜色而没有其他颜色的绘画？

我想要只有“灰色”和“黄色”的画。因此，查询应该返回“狮子和幼崽”的绘画，而不是“日落”，因为“日落”也有“橙色”。

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

Answer 1

若要检查是否使用了所有所需的颜色，请计算使用了多少种颜色：

SELECT *
FROM paintings
WHERE (SELECT COUNT(*)
       FROM color_painting
       WHERE painting_id = paintings.id
         AND color_id IN (1, 2)
      ) = 2
  AND NOT EXISTS (SELECT *
                  FROM color_painting
                  WHERE painting_id = paintings.id
                    AND color_id NOT IN (1, 2));

Answer 2

使用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