郁闷的查询一个星期的记录

Question

怎么从
open_time   a   b  c
2008-07-01  1   1  1
2008-07-02  2   2  2
2008-07-03  3   3  3
中查询open_time所在的一个星期：
open_time   a   b  c
2008-06-30  0   0  0
2008-07-01  1   1  1
2008-07-02  2   2  2
2008-07-03  3   3  3
2008-07-04  0   0  0
2008-07-05  0   0  0
2008-07-06  0   0  0
这样一个记录

Answer 1

用rownum来生成一周日期,然后关联查询

Answer 2

我这有个自己写的函数,可以取出任意一天,所在周的起始日
CREATE OR REPLACE FUNCTION choose_week (
   in_date        DATE,                                             /*基准日*/
   in_week        NUMBER,
   /*要取的周数，0本周,正数为基准日所在周之前的周，负数为基准日所在周之后的周*/
   in_sun_first   NUMBER,        /*星期日是第一天还是第一天,0最后天，1第一天*/
   start_or_end   NUMBER           /*取起始日还是截止日,0为起始日，1为截止日*/
)
   RETURN DATE
IS
   choose_day     DATE;
   base_day       DATE;
   if_sun_first   NUMBER (1);
BEGIN
   IF (in_sun_first > 1 OR in_sun_first < 0)
   THEN
      if_sun_first := 0;
   ELSE
      if_sun_first := in_sun_first;
   END IF;   SELECT DECODE (TO_CHAR (in_date, 'd'),
                  '7', in_date + 1 - if_sun_first,
                  '1', in_date + if_sun_first * 7,
                  NEXT_DAY (in_date, 7) + 1 - if_sun_first
                 )
     INTO base_day
     FROM DUAL;   IF (start_or_end = 1)
   THEN
      choose_day := TRUNC (base_day) - in_week * 7-if_sun_first + 0.99999;
   ELSE
      choose_day := TRUNC (base_day) - in_week * 7 - 6-if_sun_first;
   END IF;   RETURN choose_day;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RETURN NULL;
END choose_week;
/你可以利用这个函数来做,假设你的表是aselect b.days,nvl(a.a,0) a,nvl(a.b,0) b,nvl(a.c,0) 0
    from  a,
    (
     select choose_week(to_date('20080701','yyyymmdd'),0,0,0)+rownum-1 days
            from dual
            connect by rownum<=7
    )
where b.days=a.open_time(+)

Answer 3

hebo2005
谢谢
用rownum来生成一周日期
具体是怎么做的？

Answer 4

你要随便选定个日子，我这里用sysdate来生成当月的SELECT     TRUNC (SYSDATE, 'MM') + ROWNUM - 1
      FROM DUAL
CONNECT BY ROWNUM <= TO_NUMBER (TO_CHAR (LAST_DAY (SYSDATE), 'dd'))

Answer 5

Try it ..
SQL> select * from test_time;OPEN_TIME            A          B          C
----------- ---------- ---------- ----------
7/1/2008             1          1          1
7/2/2008             2          2          2
7/3/2008             3          3          3
7/23/2008            6          6          6SQL> 
SQL> select all_time.week_day,
  2         nvl(a, 0) as new_a,
  3         nvl(b, 0) as new_b,
  4         nvl(c, 0) as new_c
  5    from (select next_day(open_time, 1) - 7 + rn as week_day
  6            from (select rownum as rn from all_objects where rownum <= 7) ao,
  7                 test_time
  8           group by (next_day(open_time, 1) - 7 + rn)) all_time,
  9         test_time
 10   where all_time.week_day = test_time.open_time(+);WEEK_DAY         NEW_A      NEW_B      NEW_C
----------- ---------- ---------- ----------
6/30/2008            0          0          0
7/1/2008             1          1          1
7/2/2008             2          2          2
7/3/2008             3          3          3
7/4/2008             0          0          0
7/5/2008             0          0          0
7/6/2008             0          0          0
7/21/2008            0          0          0
7/22/2008            0          0          0
7/23/2008            6          6          6
7/24/2008            0          0          0
7/25/2008            0          0          0
7/26/2008            0          0          0
7/27/2008            0          0          014 rows selectedSQL>