如何筛选无正常工作摄像头的道路？SQL查询问题咨询

涉及数据表 #

表1（Cameras） #

表2（Road） #

需求说明 #

筛选出所有无正常工作摄像头（即该道路下所有isWorking均为FALSE）的道路，期望结果：

错误尝试及问题 #

原SQL代码（存在字段名/表名错误，比如误用h.highwayNum，实际应为r.roadNum）：

SELECT r.roadNum, r.length
FROM Cameras c, Road r
WHERE c.isWorking = FALSE
    AND h.highwayNum = c.highwayNum

这段代码仅筛选出存在isWorking为FALSE的道路，无法排除有正常工作摄像头的道路，得到错误结果：

正确解法 #

方法1：使用NOT EXISTS #

核心逻辑：找出Road中，不存在对应Cameras记录里isWorking = TRUE的道路，逻辑直观高效。

SELECT r.roadNum, r.length
FROM Road r
WHERE NOT EXISTS (
    SELECT 1
    FROM Cameras c
    WHERE c.roadNum = r.roadNum
      AND c.isWorking = TRUE
)

方法2：使用GROUP BY + HAVING #

先按道路分组，通过聚合函数验证分组内是否全为异常摄像头：

SELECT r.roadNum, r.length
FROM Road r
JOIN Cameras c ON r.roadNum = c.roadNum
GROUP BY r.roadNum, r.length
HAVING MAX(c.isWorking) = FALSE

注：若isWorking为字符串类型，可替换为SUM(CASE WHEN c.isWorking = 'TRUE' THEN 1 ELSE 0 END) = 0，确保统计不到正常工作的摄像头。

方法3：使用LEFT JOIN + 筛选NULL #

先关联有正常工作摄像头的道路，再筛选关联失败的道路（即无正常摄像头的道路）：

SELECT r.roadNum, r.length
FROM Road r
LEFT JOIN Cameras c 
    ON r.roadNum = c.roadNum 
    AND c.isWorking = TRUE
WHERE c.roadNum IS NULL

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