菜鸟求一道ORACLE题。内详

Question

有两张表T4,T5SQL> select * from t4;MONEY_TIME     WEEK
-------------- ----
01-1月 -07     一
02-1月 -07     二
03-1月 -07     三
04-1月 -07     四
05-1月 -07     五
06-1月 -07     六
07-1月 -07     七
08-1月 -07     一
09-1月 -07     二
10-1月 -07     三
11-1月 -07     四MONEY_TIME     WEEK
-------------- ----
12-1月 -07     五
13-1月 -07     六
14-1月 -07     七已选择14行。SQL> select * from t5;MONEY_TIME          MONEY
-------------- ----------
01-1月 -07            100
01-1月 -07            200
02-1月 -07            200
03-1月 -07            200
04-1月 -07            350
07-1月 -07            110
09-1月 -07            800
10-1月 -07           4000
11-1月 -07            250
12-1月 -07           1110
18-1月 -07           1400MONEY_TIME          MONEY
-------------- ----------
22-1月 -07           6000
30-1月 -07            600
31-1月 -07            400已选择14行。要求有以下几点.
输出一个月来资金流动情况,money_time,week,money3个字段.如果当日没有交易.则MONEY处输入0,一天有多比交易.T4,T5这两个表都是不全的.所以还要把表里没有的日期也加进去.
最后得到表应该是
money_time  week      money
1~~31号     1~7循环    有就写入.没就填0各位大哥帮忙啊.小弟跪谢.

Answer 1

select a.money_time,a.week,b.sum(nvl(b.money3,0)) as money3 from t4 a left join t5  b on a.money_time=b.money_time group by a.money_time,a.week

Answer 2

上面写错了这个才是
select a.money_time,a.week,sum(nvl(b.money3,0)) as money3 from t4 a left join t5 b on a.money_time=b.money_time group by a.money_time,a.week

Answer 3

给你举个例子： 如果这个月是统计2007-01的数据的话：=========================sql================================select mydate,to_char(mydate-1,'D'),sum(nvl(t5.money,0))
  from T4,
       t5,
       (
        select to_date('2007-01-01','yyyy-mm-dd')+rownum-1 mydate from all_objects where rownum <= to_date(to_char(last_day(to_date('2007-01-01','yyyy-mm-dd')),'yyyy-mm-dd'),'yyyy-mm-dd')-to_date((to_char(to_date('2007-01-01','yyyy-mm-dd'),'yyyy-mm')||'-01'),'yyyy-mm-dd')+1
       )
 where mydate = t4.MONEY_TIME(+)
   and mydate = t5.MONEY_TIME(+)
 group by mydate;
========================result==============================MYDATE      TO_CHAR(MYDATE-1,'D') SUM(NVL(T5.MONEY,0))
----------- --------------------- --------------------
1/1/2007    1                                      300
1/2/2007    2                                      200
1/3/2007    3                                      200
1/4/2007    4                                      350
1/5/2007    5                                        0
1/6/2007    6                                        0
1/7/2007    7                                      110
1/8/2007    1                                        0
1/9/2007    2                                      800
1/10/2007   3                                     4000
1/11/2007   4                                      250
1/12/2007   5                                     1110
1/13/2007   6                                        0
1/14/2007   7                                        0
1/15/2007   1                                        0
1/16/2007   2                                        0
1/17/2007   3                                        0
1/18/2007   4                                     1400
1/19/2007   5                                        0
1/20/2007   6                                        0MYDATE      TO_CHAR(MYDATE-1,'D') SUM(NVL(T5.MONEY,0))
----------- --------------------- --------------------
1/21/2007   7                                        0
1/22/2007   1                                     6000
1/23/2007   2                                        0
1/24/2007   3                                        0
1/25/2007   4                                        0
1/26/2007   5                                        0
1/27/2007   6                                        0
1/28/2007   7                                        0
1/29/2007   1                                        0
1/30/2007   2                                      600
1/31/2007   3                                      40031 rows selected