时间戳

首页 > 解决方案 > oracle sql组合

sql - oracle sql组合

问题描述

我有这个结果的表（例如，实际上它有更多记录）：

31.3.2020   A  1
30.4.2020   A  2
31.3.2020   B  1
30.4.2020   C  3

是否可以通过 sql 添加空的缺失值？在这种情况下：

30.4.2020   B  0
31.3.2020   C  0

谢谢

标签： sqloracle

解决方案

您可以使用anti join和NOT EXISTS如下：

SQL> with your_data (col1, col2, col3) as
  2  (select to_date('31.3.2020','dd.mm.rrrr'),   'A',  1 from dual union all
  3  select to_date('30.4.2020','dd.mm.rrrr'),   'A',  2 from dual union all
  4  select to_date('31.3.2020','dd.mm.rrrr'),   'B',  1 from dual union all
  5  select to_date('30.4.2020','dd.mm.rrrr'),   'C',  3 from dual)
  6  -- your query starts from here
  7  select distinct t1.col1, t2.col2, 0 as col3
  8  from your_data t1 join your_data t2 on t1.col1 <> t2.col1
  9  where not exists
 10  (select 1 from your_data t where t2.col2 = t.col2 and t1.col1 = t.col1)
 11  order by t1.col1, t2.col2;

COL1      C       COL3
--------- - ----------
31-MAR-20 C          0
30-APR-20 B          0

SQL>

然后UNION它与您的数据。

推荐阅读