基于日期列的不匹配记录计数

这里有一个选项。

源数据:

SQL> with
  2  source_table (sno, pick_date) as
  3    (select 1, date '2023-01-12' from dual union all
  4     select 3, date '2023-01-13' from dual union all
  5     select 5, date '2023-01-14' from dual union all
  6     select 6, date '2023-01-14' from dual union all
  7     select 7, date '2023-01-14' from dual union all
  8     select 2, date '2023-01-13' from dual
  9    ),
 10  target_Table (sno, pick_date) as
 11    (select 1, date '2023-01-12' from dual union all
 12     select 2, date '2023-01-13' from dual union all
 13     select 5, date '2023-01-14' from dual
 14    )

查询使用 minus set运算符查找目标表中缺少的行,并统计每个日期的行数:

 15  select pick_date, count(*)
 16  from (select sno, pick_date from source_table
 17        minus
 18        select sno, pick_date from target_Table
 19       )
 20  group by pick_date;

PICK_DATE    COUNT(*)
---------- ----------
13.01.2023          1
14.01.2023          2

SQL>

如果这些表中没有唯一的列,并且您不想键入10个不同的列,请使用 row_number 解析函数;然后重复我已经发布的代码:

 15  st as
 16    (select pick_date, row_number() over (partition by pick_date order by sno) rn
 17     from source_table
 18    ),
 19  tt as
 20    (select pick_date, row_number() over (partition by pick_date order by sno) rn
 21     from target_table
 22    )
 23  select pick_date, count(*)
 24  from (select rn, pick_date from st
 25        minus
 26        select rn, pick_date from tt
 27       )
 28  group by pick_date;

PICK_DATE    COUNT(*)
---------- ----------
13.01.2023          1
14.01.2023          2

SQL>