急,大虾们帮看看这个 表连接查询的问题

Question

现在有3张表:
日历表: 日历          日合计表: 年月日    合计code  合计值          表示设置表: code区分   合计code
      20090101             20090101   001      100                      1          001
     20090102             20090101   002      200                      1          002
     ...                  20090101   003      300                      1          003
     ...                  20090101   004      400                      1          004
     20090131             20090102   002      110日合计表里并不是每天的数据都有的(也有可能一条数据都没有).现在我想取出下面的数据列:
 年月日        合计code      合计值
20090101      001          100
20090101      002          200
20090101      003          300
20090101      004          40020090102      001          0
20090102      002          110
20090102      003          0
20090102      004          020090103      001          0
20090103      002          0
20090103      003          0
20090103      004          0
...
...
...
20090131      001          0
20090131      002          0
20090131      003          0
20090131      004          0请大虾们给个思路.

Answer 1

给你个思路，
先用
select to_date('20090101','yyyymmdd')+rownum-1
from dual
connect by rownum<=31
来生成31天，然后现和合计CODE生成每天的，然后再和原表做外联接，就能出来你要的结果

Answer 2

select m.t,m.c,nvl(e.val,0)
from
e,
(select d.d t,'001' c from d union
select d.d t,'002' c from d union
select d.d t,'003' c from d union
select d.d t,'004' c from d) m
where m.t=e.t(+) and m.c=e.code(+)
order by m.t,m.c;SQL> select * from d;D
--------------------
20090101
20090102
20090103
20090104SQL> select * from e;T                    COD        VAL
-------------------- --- ----------
20090101             001        100
20090102             003        100
20090102             004        100
20090103             004        100
20090104             002        100SQL> select m.t,m.c,nvl(e.val,0)
  2  from
  3  e,
  4  (select d.d t,'001' c from d unio
  5  select d.d t,'002' c from d union
  6  select d.d t,'003' c from d union
  7  select d.d t,'004' c from d) m
  8  where m.t=e.t(+) and m.c=e.code(+
  9  order by m.t,m.c;T                    C   NVL(E.VAL,0)
-------------------- --- ------------
20090101             001          100
20090101             002            0
20090101             003            0
20090101             004            0
20090102             001            0
20090102             002            0
20090102             003          100
20090102             004          100
20090103             001            0
20090103             002            0
20090103             003            0T                    C   NVL(E.VAL,0)
-------------------- --- ------------
20090103             004          100
20090104             001            0
20090104             002          100
20090104             003            0
20090104             004            0已选择16行。

Answer 3

日历表a  日合计表b 表示设置表 c
先用一个笛卡尔积把日历表与设置表相连后再与B表进行连接，这样就可以了
select d.日历,b.合计code,nvl(合计值,0) from (select * from a,c) d,b
where d.日历=b.年月日

Answer 4

SQL> select * fromRL
--------------
20090101
20090102
20090103
20090104
20090105
20090106
20090107
20090108
20090109
20090110
20090111RL
--------------
20090112
20090113
20090114
20090115
20090116
20090117
20090118
20090119
20090120
20090121
20090122RL
--------------
20090123
20090124
20090125
20090126
20090127
20090128
20090129
20090130
20090131已选择31行。
SQL> select * from b;RL             SCOD     SVALUE
-------------- ---- ----------
20090101       001         100
20090101       002         200
20090101       003         300
20090101       004         400
20090102       002         110
SQL> select * from c;     CCODE SCOD
---------- ----
         1 001
         1 002
         1 003
         1 004
SQL> select d.rl,d.scode,nvl(b.svalue,0) from
  2  ( select a.rl,c.scode from a,c ) d,b
  3  where d.rl=b.rl(+)
  4  and d.scode=b.scode(+);RL             SCOD NVL(B.SVALUE,0)
-------------- ---- ---------------
20090101       001              100
20090101       002              200
20090101       003              300
20090101       004              400
20090102       001                0
20090102       002              110
20090102       003                0
20090102       004                0
20090103       001                0
20090103       002                0
20090103       003                0RL             SCOD NVL(B.SVALUE,0)
-------------- ---- ---------------
20090103       004                0
20090104       001                0
20090104       002                0
20090104       003                0
20090104       004                0
20090105       001                0
20090105       002                0
20090105       003                0
20090105       004                0
20090106       001                0.............