有个表 tab1 ,字段stime 是date 型
如果是按天汇总求记录条数结果,那就是select to_char(stime,'yyyymmdd'),count(1)
from tab1
group by to_char(stime,'yyyymmdd')如果得到从每周一开始,每七天的汇总结果
有啥好方法不?
如果是按天汇总求记录条数结果,那就是select to_char(stime,'yyyymmdd'),count(1)
from tab1
group by to_char(stime,'yyyymmdd')如果得到从每周一开始,每七天的汇总结果
有啥好方法不?
--试试看看
select to_char(stime,'ww'),
count(1)
from tb
group by to_char(stime,'ww')
from tab1
group by to_char(stime,'fmww'))按照周分组
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;
/
我给看我写的一个函数,我通过这个函数来实现按周分组