找出特征函数. select avg(a1) from t1 group by rq - mod(to_char(rq, 'D')+5, 7)
楼主试一下这个函数: --ld_date:是用户所输入的一个日期. --ls_first:是让用户确定一周中,星期几是第一天. --函数返回值:将LD_DATE所属的一周的日期都显示出来. create or replace function f_weekdate(ld_date date,li_first integer default 0) return varchar2 as i integer; ls_ret varchar2(100); ls_temp varchar2(12); ld_next date; ls_first varchar2(10); begin case li_first when 1 then ls_first:='星期一'; when 2 then ls_first:='星期二'; when 3 then ls_first:='星期三'; when 4 then ls_first:='星期四'; when 5 then ls_first:='星期五'; when 6 then ls_first:='星期六'; else ls_first:='星期日'; end case; ls_ret:=''; ld_next:=next_day(ld_date,ls_first); for i in 1..7 loop ls_temp:=to_char(ld_next - i,'yyyy-mm-dd'); ls_ret:=ls_temp||' '||ls_ret; end loop; return ls_ret; end; 测试:select f_weekdate(to_date('2008-06-24','yyyy-mm-dd')) from dual; F_WEEKDATE(TO_DATE('2008-06-24','YYYY-MM-DD')) ---------------------------------------------------------------------------------------------------- 2008-06-22 2008-06-23 2008-06-24 2008-06-25 2008-06-26 2008-06-27 2008-06-28已选择 1 行。 select f_weekdate(to_date('2008-06-24','yyyy-mm-dd'),3) from dual;F_WEEKDATE(TO_DATE('2008-06-24','YYYY-MM-DD'),3) ---------------------------------------------------------------------------------------------------- 2008-06-18 2008-06-19 2008-06-20 2008-06-21 2008-06-22 2008-06-23 2008-06-24已选择 1 行。
SELECT ROUND(SYSDATE,'DAY') FROM DUAL --四舍五入到最靠近的星期日
SELECT NEXT_DAY(SYSDATE,'星期日') FROM DUAL --下一个星期日的日期
rq1是任意选择的日期也就是参数
我要按日期查询数据,比如按月查询select a1 from t1 where to_char(rq,'yyyymm')=to_char(rq1,'yyyymm');
这样可以实现按月查询,问题是我想做一个按周查询。
要怎么做呢?在WHERE语句中判断周。
帮帮忙。
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;
/另外个函数是用来显示某个周是从几号到几号的CREATE OR REPLACE FUNCTION get_week_daytoday (
in_year VARCHAR2, /*年份*/
in_week VARCHAR2, /*周数*/
in_sun_first NUMBER, /*星期日是第一天还是第一天,0最后天,1第一天*/
year_flag NUMBER /*是否显示年份,0不显示,1显示2位,2显示四位*/
)
RETURN VARCHAR2
IS
week_day VARCHAR2 (30);
year_first_week_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 (TO_DATE (in_year || '/01/01', 'YYYY/MM/DD'), 'd'),
'7', TO_DATE (in_year || '/01/01', 'YYYY/MM/DD')
- 6
- if_sun_first,
NEXT_DAY (TO_DATE (in_year || '/01/01', 'YYYY/MM/DD'), 7)
- 6
- if_sun_first
)
INTO year_first_week_day
FROM DUAL; IF (year_flag = 1)
THEN
week_day :=
TO_CHAR (year_first_week_day + TO_NUMBER (in_week) * 7 - 6,
'YY/MM/DD'
)
|| '-'
|| TO_CHAR (year_first_week_day + TO_NUMBER (in_week) * 7,
'YY/MM/DD');
ELSE
IF (year_flag = 0)
THEN
week_day :=
TO_CHAR (year_first_week_day + TO_NUMBER (in_week) * 7 - 6,
'MM/DD'
)
|| '-'
|| TO_CHAR (year_first_week_day + TO_NUMBER (in_week) * 7,
'MM/DD');
ELSE
week_day :=
TO_CHAR (year_first_week_day + TO_NUMBER (in_week) * 7 - 6,
'YYYY/MM/DD'
)
|| '-'
|| TO_CHAR (year_first_week_day + TO_NUMBER (in_week) * 7,
'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_daytoday;
/
where date between choose_week(sysdate,0,0,0) and choose_week(sysdate,0,0,1)这样就行了
可以这样,where rq between NEXT_DAY(SYSDATE,'星期日')-6 and NEXT_DAY(SYSDATE,'星期日')
SELECT TO_DATE('2008-02-24', 'yyyy-mm-dd') - TO_CHAR(TO_DATE('2008-02-24', 'yyyy-mm-dd'), 'd') + 1,
TO_DATE('2008-02-24', 'yyyy-mm-dd') - TO_CHAR(TO_DATE('2008-02-24', 'yyyy-mm-dd'), 'd') + 7
FROM DUAL;
between round(next_day(SYSDATE - 1, '星期日') - 6) and
,round(next_day(SYSDATE - 1, '星期日'))系统默认星期日是一周的开始,所以应该要这样.
TO_NUMBER((TO_CHAR(TO_DATE('2008-05-31', 'YYYY-mm-dd'), 'd')), 9)) + 2,
TRUNC(TO_DATE('2008-05-31', 'YYYY-mm-dd') -
TO_NUMBER((TO_CHAR(TO_DATE('2008-05-31', 'YYYY-mm-dd'), 'd')), 9)) + 8
FROM DUAL;
select avg(a1) from t1 group by rq - mod(to_char(rq, 'D')+5, 7)
--ld_date:是用户所输入的一个日期.
--ls_first:是让用户确定一周中,星期几是第一天.
--函数返回值:将LD_DATE所属的一周的日期都显示出来.
create or replace function f_weekdate(ld_date date,li_first integer default 0)
return varchar2
as
i integer;
ls_ret varchar2(100);
ls_temp varchar2(12);
ld_next date;
ls_first varchar2(10);
begin
case li_first
when 1 then
ls_first:='星期一';
when 2 then
ls_first:='星期二';
when 3 then
ls_first:='星期三';
when 4 then
ls_first:='星期四';
when 5 then
ls_first:='星期五';
when 6 then
ls_first:='星期六';
else
ls_first:='星期日';
end case;
ls_ret:='';
ld_next:=next_day(ld_date,ls_first);
for i in 1..7
loop
ls_temp:=to_char(ld_next - i,'yyyy-mm-dd');
ls_ret:=ls_temp||' '||ls_ret;
end loop;
return ls_ret;
end;
测试:select f_weekdate(to_date('2008-06-24','yyyy-mm-dd')) from dual;
F_WEEKDATE(TO_DATE('2008-06-24','YYYY-MM-DD'))
----------------------------------------------------------------------------------------------------
2008-06-22 2008-06-23 2008-06-24 2008-06-25 2008-06-26 2008-06-27 2008-06-28已选择 1 行。
select f_weekdate(to_date('2008-06-24','yyyy-mm-dd'),3) from dual;F_WEEKDATE(TO_DATE('2008-06-24','YYYY-MM-DD'),3)
----------------------------------------------------------------------------------------------------
2008-06-18 2008-06-19 2008-06-20 2008-06-21 2008-06-22 2008-06-23 2008-06-24已选择 1 行。