sql - 简化嵌套查询

我想获取在给定日期添加和删除的记录（基于id_external），因此我使用了以下查询，这给了我预期的结果，但是这花费了很多时间，并且LOGICALREAD太高了，...有人可以简化一下吗

SELECT ar.*
      FROM t_row_data ar
      WHERE ar.id_instance     IS NULL
      AND ar.id_category     IS NULL
      AND ar.source_name      ='SomeSource'
      AND ar.eco_date        IN (date '2017-12-22', date '2017-12-21')
      AND ar.active           = 'Y'
      AND ar.id_external IN
        (SELECT ar.id_external
              FROM t_row_data ar
              WHERE ar.id_instance     IS NULL
              AND ar.id_category     IS NULL
              AND ar.source_name      ='SomeSource'
              AND ar.eco_date        IN (date '2017-12-22', date '2017-12-21')
              AND ar.active           = 'Y'
              GROUP BY ar.id_external
              HAVING COUNT(1) = 1)

最佳答案

假设表t_row_data具有列id_external, id_instance, id_category, source_name, eco_date, active, col7, col8, ...，则可以这样重写查询：

select   id_external, null as id_instance, null as id_category,
         'SomeSource' as source_name, max(eco_date) as eco_date, 'Y' as active,
         max(col7) as col7, max(col8) as col8, ...
from     t_row_data
where    id_instance is null
  and    id_category is null
  and    source_name = 'SomeSource'
  and    eco_date    between date '2017-12-21' and date '2017-12-22'
  and    active      = 'Y'
group by id_external
having   count(*) = 1
;

关于sql - 简化嵌套查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48001877/