如何在oracle中过滤子查询？

Question

我使用的查询是

select count(*) as count,
       TO_CHAR(firstTable1.firstTable_date, 'DD-MON-YY') as  dateAndTime
from  (
  select *
  from   firstTable
  where  firstTable_status = 'Open'
  and    firstTable_date   > sysdate-10
) firstTable1
group by TO_CHAR(firstTable1.firstTable_date, 'DD-MON-YY')

这会让我在5月11日数到1

并计数为1，表示5月17日

示例表数据如下

firstTable

                    id        firstTable_status  firstTable_date

                   A-123         Open                11-may-17
                   B-123         Open                09-may-17

secondTable

  secondTable_Id    firstTable_id      secondTable_Action  secondTable_date

       XX1       A-123            started                 11-may-17
       XX2       A-123            Assigned                11-may-17
       XX3       A-123            action2                 11-may-17
       XX4       A-123            action3                 11-may-17
       XX5       A-123            action4                 11-may-17
       XX6       A-123            action5                 11-may-17
       XX7       A-123            closed                  11-may-17

       MN1       B-123            started                09-may-17
       MN1       B-123            action1                10-may-17
       MN1       B-123            closed                 11-may-17

是否可以修改此查询，例如

如果在同一日期关闭同一id的计数，则查询不应返回该id的secondTable_Action

例如，firstTable中的id A-123在5月11日打开，并在同一天关闭，该查询的第二个表中的条目应在5月11日返回count 0，而id B-123在不同的日期关闭，因此它应在9月17日返回count 1

Answer 1

SELECT dt,
       SUM( open_closed ) AS "count"
FROM   (
  SELECT f.id,
         TRUNC( f.firstTable_date ) as dt,
         COUNT( CASE s.secondtable_action WHEN 'started' THEN 1 END )
           - COUNT( CASE s.secondtable_action WHEN 'closed' THEN 1 END ) AS open_closed
  FROM   firstTable f
         INNER JOIN secondtable s
         ON ( f.id = s.firsttable_id )
  WHERE  firstTable_date   > SYSDATE - INTERVAL '10' DAY
  GROUP BY f.id,
         TRUNC( f.firstTable_date )
  HAVING COUNT( CASE s.secondtable_action WHEN 'started' THEN 1 END ) > 0
)
GROUP BY dt

Answer 2

您可以使用not exists或类似的方法删除闭包：

select count(*) as count,
       to_char(t.firstTable_date, 'DD-MON-YY') as dateAndTime
from firstTable t
where t.firstTable_status = 'Open' and
      t.firstTable_date > sysdate - 10 and
      not exists (select 1
                  from secondTable t2
                  where t2.id = t.id and
                        t2.firstTable_date = t.firstTable_date and
                        t2.firstTable_status = 'Closed'
                 )
group by to_char(t.firstTable_date, 'DD-MON-YY')
order by min(firstTable_date);

备注：

• 如果firstTable_date有时间成分，那么子句应该考虑到这一点：
  • 可以使用firstTable_date而不是to_char()。如果你打算使用to_char()，我建议你使用YYYY-MM-DD格式。这是ISO标准，适用于排序。
  • I添加了一个order by，因此日期将按顺序排列。
  • 不需要子查询。