Oracle:获取所有月份的数据，如果没有数据则为 0

Question

我有一个 Mview，它通过 idNumber 带来数据组和 Month .所以如果没有特定月份的数据，我想显示 0。这是我的查询:

select MonthName, myCost, myNumber 
  from
     (
       select MONTH mm, myCost, myNumber
         from myOracle_mv
     ) myTotals,
     (
       select to_char(date '2012-12-1' + numtoyminterval(level,'month'), 'mm') MonthName
        from dual
      connect by level <= 12
     ) ALLMONTHS
where mm = MonthName

所以我期待:

Month Number Data
-----------------------
1     abc123 4444
2     0
3     abc123 4444
4     abc123 4444
5     0
6     abc123 4444
7     abc123 4444
8     0
9     abc123 4444
10    abc123 4444
11    0
12    abc123 4444

相反，我仍然得到:

1    abc123  4444
3    abc123  4444
4    abc123  4444
6    abc123  4444
7    abc123  4444
9    abc123  4444
10   abc123  4444
12   abc123  4444

有任何想法吗？

谢谢!

编辑:感谢您的回答。我确实在我的查询中有外连接，但忘记输入，因为正在专注于更改表/列的名称。

所以是的，我已经尝试过 OUTER JOIN，但仍然没有得到预期的结果。非常感谢任何反馈。

编辑:这是 myOracle_MV 上的数据:

3777.24     AAA 1   2012
49973.12    AAA 2   2012
4049.91     AAA 3   2012
469.485     AAA 4   2012
5872.22     AAA 5   2012
65837.71    AAA 6   2012
566.23      AAA 7   2012
18432.95    AAA 8   2012
4337.75     AAA 12  2011
18811       BBB 1   2012
29872.67    BBB 2   2012
29068.55    BBB 3   2012
264957.8    BBB 4   2012
67673       BBB 5   2012
855.02      BBB 6   2012
5226.1      BBB 7   2012
2663.24     BBB 8   2012
5490.58     BBB 12  2011
3845.47     CCC 1   2012
3050.54     CCC 2   2012
3784.44     CCC 3   2012
799.73      CCC 4   2012
124884.2    CCC 5   2012
5157.24     CCC 6   2012
19184.78    CCC 7   2012
2280.05     CCC 8   2012
107.07      DDD 3   2012
181.78      DDD 4   2012
110.09      DDD 5   2012
18016.19    DDD 6   2012
1772.95     DDD 7   2012
63.32       DDD 8   2012

Answer 1

与现有答案非常相似，但是:

select months.month, mv.mycost, coalesce(mv.mynumber, 0) as mynumber
from (
  select to_char(date '1970-01-01'
    + numtoyminterval(level - 1, 'month'), 'mm') as month
  from dual
  connect by level <= 12) months
left join myoracle_mv mv
on mv.month = months.month
order by months.month, mv.mycost, mv.mynumber;

用你发布的数据给出这个:

MONTH MYCOST   MYNUMBER
----- ------ ----------
01    AAA       3777.24 
01    BBB         18811 
01    CCC       3845.47 
02    AAA      49973.12 
02    BBB      29872.67 
02    CCC       3050.54 
03    AAA       4049.91 
03    BBB      29068.55 
03    CCC       3784.44 
03    DDD        107.07 
04    AAA       469.485 
04    BBB      264957.8 
04    CCC        799.73 
04    DDD        181.78 
05    AAA       5872.22 
05    BBB         67673 
05    CCC      124884.2 
05    DDD        110.09 
06    AAA      65837.71 
06    BBB        855.02 
06    CCC       5157.24 
06    DDD      18016.19 
07    AAA        566.23 
07    BBB        5226.1 
07    CCC      19184.78 
07    DDD       1772.95 
08    AAA      18432.95 
08    BBB       2663.24 
08    CCC       2280.05 
08    DDD         63.32 
09                    0 
10                    0 
11                    0 
12    AAA       4337.75 
12    BBB       5490.58 

 35 rows selected

如果您希望 mynumber 中出现零列然后你可以做到这一点:

select months.month, mv.mycost, coalesce(mv.mynumber, 0) as mynumber

这使:

...
08    DDD         63.32 
09                    0 
10                    0 
11                    0 
12    AAA       4337.75 
...

从对 Jafar 的回答的评论看来，您可能自己已经做到了那么远，但您希望所有 mycost 的值都为零。所有月份的值。如果是这种情况，那么您需要获取 mycost 的可能值列表。和外部连接也是如此。这将采用 MV 中的所有值:

select months.month, costs.mycost, coalesce(mv.mynumber, 0) as mynumber
from (
  select to_char(date '1970-01-01'
    + numtoyminterval(level - 1, 'month'), 'mm') as month
  from dual
  connect by level <= 12) months
cross join (
  select distinct mycost
  from myoracle_mv) costs
left join myoracle_mv mv
on mv.month = months.month
and mv.mycost = costs.mycost
order by months.month, costs.mycost, mv.mynumber;

并给出:

MONTH MYCOST   MYNUMBER
----- ------ ----------
01    AAA       3777.24 
01    BBB         18811 
01    CCC       3845.47 
01    DDD             0 
02    AAA      49973.12 
02    BBB      29872.67 
02    CCC       3050.54 
02    DDD             0 
03    AAA       4049.91 
03    BBB      29068.55 
03    CCC       3784.44 
03    DDD        107.07 
04    AAA       469.485 
04    BBB      264957.8 
04    CCC        799.73 
04    DDD        181.78 
05    AAA       5872.22 
05    BBB         67673 
05    CCC      124884.2 
05    DDD        110.09 
06    AAA      65837.71 
06    BBB        855.02 
06    CCC       5157.24 
06    DDD      18016.19 
07    AAA        566.23 
07    BBB        5226.1 
07    CCC      19184.78 
07    DDD       1772.95 
08    AAA      18432.95 
08    BBB       2663.24 
08    CCC       2280.05 
08    DDD         63.32 
09    AAA             0 
09    BBB             0 
09    CCC             0 
09    DDD             0 
10    AAA             0 
10    BBB             0 
10    CCC             0 
10    DDD             0 
11    AAA             0 
11    BBB             0 
11    CCC             0 
11    DDD             0 
12    AAA       4337.75 
12    BBB       5490.58 
12    CCC             0 
12    DDD             0 

 48 rows selected 

但希望你有另一张 table 可以容纳可能的 mycost值(假设它代表成本中心之类的东西，而不是价格；有点难以分辨是什么)，您可以使用它代替子查询。

SQL Fiddle .

另请注意，如果您想添加过滤器，例如要将数据限制在特定年份，您需要在 left join 中执行此操作条款，而不是 where子句，或者您将外部连接恢复为内部连接。例如， adding this :

where mv.year = 2011

意味着你只得到了两行:

MONTH MYCOST   MYNUMBER
----- ------ ----------
12    AAA       4337.75 
12    BBB       5490.58 

但是如果你 made than another condition on the outer join你仍然会得到 48 行，其中 46 行具有零，两个具有上述值:

...
left join myoracle_mv mv
on mv.month = months.month
and mv.mycost = costs.mycost
and mv.year = 2011
order by months.month, costs.mycost, mv.mynumber;

...
11    CCC             0 
11    DDD             0 
12    AAA       4337.75 
12    BBB       5490.58 
12    CCC             0 
12    DDD             0 

 48 rows selected