如何在SQLite中筛选同时使用红色和绿色的画作？

你的原SQL有两个核心问题：

1. 未显式关联colors和paint表，直接在WHERE子句中引用这两个表的字段会导致语法错误或非预期的笛卡尔积；
2. colors.name = 'Green' AND colors.name = 'Red'是逻辑矛盾——单条颜色记录不可能同时是两种颜色，因此永远查不到结果。

用GROUP BY和HAVING实现需求的正确写法如下：

SELECT p.name
FROM painting p
JOIN paint pt ON p.id = pt.painting_id
JOIN colors c ON pt.color_id = c.id
WHERE c.name IN ('Green', 'Red')
GROUP BY p.id, p.name
HAVING COUNT(DISTINCT c.name) = 2;

语句说明： #

• JOIN关联表：通过paint中间表，将painting和colors建立正确的关联关系，确保只查询画作与对应颜色的匹配记录；
• WHERE筛选颜色范围：先缩小范围到只包含绿色或红色的记录；
• GROUP BY分组：按画作的主键id和名称name分组，确保每个分组对应一幅画作；
• HAVING条件过滤：COUNT(DISTINCT c.name) = 2确保分组内同时包含两种不同的颜色（DISTINCT避免同一颜色重复使用导致的计数误差）。

如果你更倾向于用存在性验证的方式，也可以用双重EXISTS子查询：

SELECT name
FROM painting p
WHERE EXISTS (
    SELECT 1
    FROM paint pt1
    JOIN colors c1 ON pt1.color_id = c1.id
    WHERE pt1.painting_id = p.id AND c1.name = 'Green'
)
AND EXISTS (
    SELECT 1
    FROM paint pt2
    JOIN colors c2 ON pt2.color_id = c2.id
    WHERE pt2.painting_id = p.id AND c2.name = 'Red'
);

这种方式通过两次独立的存在性检查，确认画作同时关联了绿色和红色记录，逻辑更直观。

内容的提问来源于stack exchange，提问作者Programmingisnewforme