基于关系表检索满足双条件的image_id的SQL查询需求

数据表 #

检索要求 #

• 目标image_id对应的摄像头必须检测过物体（要么自身有OBJECT记录，要么所属摄像头检测过符合条件的物体）
• 该物体被至少两个不同的摄像头检测过

预期输出 #

1,3,4

SQL实现方案 #

-- 先筛选出被至少两个不同摄像头检测到的物体
WITH target_objects AS (
    SELECT o.type_id
    FROM your_table o
    JOIN your_table c ON o.image_id = c.image_id AND c.type = 'CAMERA'
    WHERE o.type = 'OBJECT'
    GROUP BY o.type_id
    HAVING COUNT(DISTINCT c.type_id) >= 2
),
-- 再找出所有检测过这些物体的摄像头
target_cameras AS (
    SELECT DISTINCT c.type_id
    FROM your_table c
    JOIN your_table o ON c.image_id = o.image_id AND o.type = 'OBJECT'
    WHERE o.type_id IN (SELECT type_id FROM target_objects)
)
-- 最后获取所有属于这些摄像头的image_id
SELECT DISTINCT image_id
FROM your_table
WHERE type_id IN (SELECT type_id FROM target_cameras)

结果说明 #

• 1和3：分别对应摄像头aaa和ccc，都检测到物体123，而123被两个不同摄像头识别，满足条件。
• 4：属于摄像头aaa，而aaa检测过符合条件的物体123，因此也被纳入结果。
• 5和6对应的物体456、789仅被摄像头ddd单独检测，不符合要求，所以排除。

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