如何过滤SQL查询，仅显示含重复OBJECT_ID的记录？

要筛选出同时满足所有指定属性条件的OBJECT_ID记录，核心思路是先识别出那些覆盖所有条件的ID，再用这些ID过滤原查询结果。以下提供两种可行的修改方案：

方案一：用子查询统计符合条件的属性数量 #

通过子查询统计每个OBJECT_ID匹配的属性-值组合数量，仅保留数量等于条件总数（此处为2）的ID，再关联原查询结果：

SELECT t.*
FROM (
    -- 原查询逻辑，保留所有符合单个条件的记录
    SELECT c.object_id             AS "OBJECT_ID",
           c.object_name           AS "OBJECT_NAME",
           cs.object_status        AS "OBJECT_STATUS",
           pn.name                AS "PROPERTY_NAME",
           pv.value_literal       AS "PROPERTY_VALUE",
           ca.object_status        AS "ACTIVE",
           Max(cl.execution_time) AS "LAST_OBJECT_EXECUTION"
    FROM   object_log_hist cl
           JOIN object_table c
            ON c.object_id = cl.object_id
           JOIN object_status ca
            ON ca.object_status_id = c.status
           JOIN object_property cp
             ON cl.object_id = cp.object_id
           JOIN object_property_name pn
             ON cp.property_id = pn.id
           JOIN object_property_valid_value pv
             ON pn.id = pv.name_id 
           JOIN object_status cs
             ON cs.object_status_id = cl.execution_status    
    WHERE  (
            (pn.name = 'propertyName1' AND pv.value_literal = 'production') OR
            (pn.name = 'propertyName2' AND pv.value_literal = 'testing')
           )
    AND    cs.object_status = 'Complete'
    GROUP  BY c.object_id,
              c.object_name,
              cs.object_status,
              ca.object_status,
              pn.name,
              pv.value_literal
) t
-- 筛选出同时满足两个条件的OBJECT_ID
WHERE t.OBJECT_ID IN (
    SELECT cl.object_id
    FROM   object_log_hist cl
           JOIN object_property cp
             ON cl.object_id = cp.object_id
           JOIN object_property_name pn
             ON cp.property_id = pn.id
           JOIN object_property_valid_value pv
             ON pn.id = pv.name_id 
           JOIN object_status cs
             ON cs.object_status_id = cl.execution_status    
    WHERE  (
            (pn.name = 'propertyName1' AND pv.value_literal = 'production') OR
            (pn.name = 'propertyName2' AND pv.value_literal = 'testing')
           )
    AND    cs.object_status = 'Complete'
    GROUP  BY cl.object_id
    -- 统计每个ID匹配的唯一属性-值组合数，等于2则表示覆盖所有条件
    HAVING COUNT(DISTINCT CONCAT(pn.name, ':', pv.value_literal)) = 2
)
ORDER BY t.OBJECT_ID;

方案二：用EXISTS验证双条件存在性 #

对每一行记录，通过EXISTS子查询验证同一个OBJECT_ID是否存在另一条件的匹配记录，确保仅保留同时满足两个条件的ID：

SELECT c.object_id             AS "OBJECT_ID",
       c.object_name           AS "OBJECT_NAME",
       cs.object_status        AS "OBJECT_STATUS",
       pn.name                AS "PROPERTY_NAME",
       pv.value_literal       AS "PROPERTY_VALUE",
       ca.object_status        AS "ACTIVE",
       Max(cl.execution_time) AS "LAST_OBJECT_EXECUTION"
FROM   object_log_hist cl
       JOIN object_table c
        ON c.object_id = cl.object_id
       JOIN object_status ca
        ON ca.object_status_id = c.status
       JOIN object_property cp
         ON cl.object_id = cp.object_id
       JOIN object_property_name pn
         ON cp.property_id = pn.id
       JOIN object_property_valid_value pv
         ON pn.id = pv.name_id 
       JOIN object_status cs
         ON cs.object_status_id = cl.execution_status    
WHERE  (
        (pn.name = 'propertyName1' AND pv.value_literal = 'production') OR
        (pn.name = 'propertyName2' AND pv.value_literal = 'testing')
       )
AND    cs.object_status = 'Complete'
-- 验证当前ID是否存在另一条件的匹配记录
AND EXISTS (
    SELECT 1
    FROM object_log_hist cl2
           JOIN object_property cp2
             ON cl2.object_id = c.object_id
           JOIN object_property_name pn2
             ON cp2.property_id = pn2.id
           JOIN object_property_valid_value pv2
             ON pn2.id = pv2.name_id 
           JOIN object_status cs2
             ON cs2.object_status_id = cl2.execution_status    
    WHERE cl2.object_id = c.object_id
    AND cs2.object_status = 'Complete'
    AND (
        -- 当前行是第一个条件时，检查是否存在第二个条件的记录
        (pn.name = 'propertyName1' AND pv.value_literal = 'production' 
         AND pn2.name = 'propertyName2' AND pv2.value_literal = 'testing')
        -- 当前行是第二个条件时，检查是否存在第一个条件的记录
        OR (pn.name = 'propertyName2' AND pv.value_literal = 'testing' 
            AND pn2.name = 'propertyName1' AND pv2.value_literal = 'production')
    )
)
GROUP  BY c.object_id,
          c.object_name,
          cs.object_status,
          ca.object_status,
          pn.name,
          pv.value_literal
ORDER BY c.object_id;

方案说明 #

• 方案一适合条件较多的场景（比如3个及以上条件），只需修改HAVING中的数字即可；
• 方案二在多数数据库中性能更优，可利用索引快速验证条件存在性。

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