甲骨文SQL组合

Question

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

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

谢谢

Answer 1

您可以使用cross join生成行，然后left join引入现有值：

select  c1.col1, c2.col2, coalesce(col3, 0) as col3
from (select distinct col1 from t) c1 cross join
(select distinct col2 from t) c2 left join
t
on t.col1 = c1.col1 and t.col2 = c2.col2;

这将返回所有行(包括具有现有值的行(。 您可以添加where t.col1 is null以仅获取其他行。

Answer 2

请使用以下查询，

select col1, col2,
case when (col3 is null or col3 = '') then 0 else col3 as col3
from table;

如果您没有任何空字符串，则可以在下面使用，

select col1, col2,
case when nvl(col3, 0) as col3
from table;

Answer 3

您可以按如下方式使用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它。