求SQL Server左连接并按右表特定值过滤的SELECT语句

问题描述 #

需要编写SQL语句，从tblObject（颜色名称表）和tblColorCombination（颜色组合表）中，仅保留那些在tblColorCombination里同时拥有'Red'和'Blue'两种颜色的tblObject记录，关联后返回对应颜色组合的详细信息。
原尝试的左连接语句无法过滤掉仅单一颜色的记录（比如Rose只有'Red'、Sky只有'Blue'），需要调整逻辑实现精准筛选。

表结构 #

tblObject（左表）

objID    objName
1        Violet
2        Rose 
3        Grass
4        Sky
5        Magenta

tblColorCombination（右表）

ccID    objID     Color
1       1         Red
2       1         Blue
3       2         Red
4       3         Green
5       4         Blue
6       5         Blue
7       5         Red

期望结果 #

ccID    objID    objName   Color
1       1         Violet    Red
2       1         Violet    Blue
6       5         Magenta   Blue
7       5         Magenta   Red

解决方案 #

方法一：GROUP BY + HAVING 筛选符合条件的objID

先通过子查询找出同时包含'Red'和'Blue'的objID，再关联两张表获取详细记录：

SELECT T1.ccID, T0.objID, T0.objName, T1.Color
FROM tblObject T0
INNER JOIN tblColorCombination T1 
    ON T1.objID = T0.objID 
    AND T1.Color IN ('Red', 'Blue')
WHERE T0.objID IN (
    SELECT objID
    FROM tblColorCombination
    WHERE Color IN ('Red', 'Blue')
    GROUP BY objID
    HAVING COUNT(DISTINCT Color) = 2
)
ORDER BY T0.objID, T1.Color;

逻辑说明：

• 子查询针对颜色组合表，先过滤出'Red'/'Blue'的记录，按objID分组后，通过COUNT(DISTINCT Color) = 2确保该objID同时拥有两种颜色。
• 主查询仅关联符合条件的objID，且只保留'Red'/'Blue'的颜色项，最终得到目标结果。

方法二：双重EXISTS 验证颜色存在性

通过两个EXISTS子查询分别验证当前objID是否存在'Red'和'Blue'的记录，仅同时满足的才被保留：

SELECT T1.ccID, T0.objID, T0.objName, T1.Color
FROM tblObject T0
INNER JOIN tblColorCombination T1 
    ON T1.objID = T0.objID 
    AND T1.Color IN ('Red', 'Blue')
WHERE EXISTS (
    SELECT 1
    FROM tblColorCombination cc1
    WHERE cc1.objID = T0.objID AND cc1.Color = 'Red'
)
AND EXISTS (
    SELECT 1
    FROM tblColorCombination cc2
    WHERE cc2.objID = T0.objID AND cc2.Color = 'Blue'
)
ORDER BY T0.objID, T1.Color;

逻辑说明：

• 两个EXISTS分别检查当前objID是否存在对应颜色的记录，只有同时满足两种颜色存在的objID才会被纳入结果集。
• 这种写法逻辑直观，对索引友好，适合数据量较大的场景。

原查询问题分析 #

原查询使用LEFT JOIN会保留tblObject中的所有记录，即使该objID仅拥有单一颜色或无目标颜色（比如Grass仅含Green，原查询会返回其objID但ccID/Color为NULL），无法实现“仅保留同时拥有两种颜色”的筛选需求。换成INNER JOIN并配合筛选条件，即可过滤掉不符合要求的记录。

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