如何编写SQL查询无关联活跃Store的PhysicalLocation记录

原SQL错误原因 #

你写的SQL逻辑是筛选「至少关联了1个Inactive状态Store」的PhysicalLocation，和需求「完全没有关联任何Active状态Store的PhysicalLocation」逻辑不一致：LocationId=100虽然有Inactive的Store，但同时也有Active的Store，不符合要求所以不应该返回。

正确实现方式 #

写法1：使用NOT EXISTS（推荐，兼容性好、性能优）

SELECT * 
FROM PhysicalLocation pl
WHERE NOT EXISTS (
    SELECT 1 
    FROM Store s 
    WHERE s.LocationId = pl.LocationId 
    AND s.Status = 'Active'
);

该写法逻辑和需求直接匹配，只要当前PhysicalLocation没有关联任何状态为Active的Store就会返回，会同时包含没有关联任何Store的Location（示例中的LocationId=102）。

如果你的需求是只返回「至少关联了1个Store，且所有关联Store都是Inactive」的记录（和你给出的预期结果一致，仅返回LocationId=101），调整为：

SELECT * 
FROM PhysicalLocation pl
WHERE NOT EXISTS (
    SELECT 1 
    FROM Store s 
    WHERE s.LocationId = pl.LocationId 
    AND s.Status = 'Active'
) AND EXISTS (
    SELECT 1 
    FROM Store s 
    WHERE s.LocationId = pl.LocationId
);

写法2：使用LEFT JOIN + 空值判断

-- 返回包含无关联Store的结果
SELECT DISTINCT pl.*
FROM PhysicalLocation pl
LEFT JOIN Store s 
    ON pl.LocationId = s.LocationId 
    AND s.Status = 'Active'
WHERE s.StoreID IS NULL;

通过左连接关联Active状态的Store，连接不上的就是没有关联Active Store的PhysicalLocation，加DISTINCT避免重复返回。

如果需要匹配你的预期结果，调整为：

SELECT DISTINCT pl.*
FROM PhysicalLocation pl
JOIN Store s_all ON pl.LocationId = s_all.LocationId
LEFT JOIN Store s_active 
    ON pl.LocationId = s_active.LocationId 
    AND s_active.Status = 'Active'
WHERE s_active.StoreID IS NULL;

写法3：使用GROUP BY + 聚合条件判断

-- 匹配你的预期结果，仅返回有关联Store且全为Inactive的记录
SELECT pl.LocationId, pl.LocationAddress
FROM PhysicalLocation pl
JOIN Store s ON pl.LocationId = s.LocationId
GROUP BY pl.LocationId, pl.LocationAddress
HAVING COUNT(CASE WHEN s.Status = 'Active' THEN 1 END) = 0;

按PhysicalLocation分组后统计每个位置关联的Active状态Store数量，数量为0的就是符合要求的记录。

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