问个按时间段汇总统计的问题

Question

有个表 tab1  ,字段stime 是date 型
如果是按天汇总求记录条数结果，那就是select to_char(stime,'yyyymmdd'),count(1)
from tab1 
group by to_char(stime,'yyyymmdd')如果得到从每周一开始，每七天的汇总结果
有啥好方法不？

Answer 1

--试试看看
select to_char(stime,'ww'),
       count(1)
from tb
group by to_char(stime,'ww')

Answer 2

select to_char(stime,'fmww')),count(1)
from tab1 
group by to_char(stime,'fmww'))按照周分组

Answer 3

CREATE OR REPLACE FUNCTION SHOP88.get_week_day2day (in_date         DATE, /*传入日期*/
                                                   in_sun_first    NUMBER, /*星期日是第一天还是第一天,0最后天，1第一天*/
                                                   year_flag       NUMBER /*是否显示年份，0不显示，1显示2位,2显示四位*/
                                                                         )
   RETURN VARCHAR2
IS
   week_start_day   DATE;
   week_end_day     DATE;
   week_day         VARCHAR (22);
   ls_diff          NUMBER (1);                                     -- 与周初差异天数
   /******************************************************************************
   NAME:       get_week_day2day
+--------------------------------------------
|
| Author:      何波
|
| Create date: 2010/08/30
|
| Description:  获得目标日的所在周起始及截止日   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   .
    NOTES:   Automatically available Auto Replace Keywords:
      Object Name:     get_week_day2day
      Sysdate:         2010/08/30
      Date and Time:   2010/08/30, 20:59:52
      Username:        (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)******************************************************************************/
BEGIN
   IF (in_sun_first = 0)
   THEN
      ls_diff := TO_NUMBER (TO_CHAR (in_date, 'd')) - 2;
   ELSE
      ls_diff := TO_NUMBER (TO_CHAR (in_date, 'd')) - 1;
   END IF;   IF ls_diff < 0
   THEN
      ls_diff := 6;
   END IF;   week_start_day := in_date - ls_diff;
   week_end_day := in_date + (6 - ls_diff);   IF (year_flag = 1)
   THEN
      week_day :=
            TO_CHAR (week_start_day, 'YY/MM/DD')
         || '-'
         || TO_CHAR (week_end_day, 'YY/MM/DD');
   ELSE
      IF (year_flag = 0)
      THEN
         week_day :=
               TO_CHAR (week_start_day, 'MM/DD')
            || '-'
            || TO_CHAR (week_end_day, 'MM/DD');
      ELSE
         week_day :=
               TO_CHAR (week_start_day, 'YYYY/MM/DD')
            || '-'
            || TO_CHAR (week_end_day, 'YYYY/MM/DD');
      END IF;
   END IF;   RETURN week_day;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RETURN NULL;
END get_week_day2day;
/
我给看我写的一个函数，我通过这个函数来实现按周分组