求SQL 在oracle中要统计特殊周的数据将正常周(从周日到周六)改成从周五到周四,用sql怎么来写? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 不太明白楼主什么意思,不可以用下面的条件来完成统计吗?select * from table twhere to_char(statDate,'d') = 6;--周五, /-- 试一下:select to_char(sysdate,'DAY') from dual; where to_char(coldate,'day') in ('星期四','星期五') 你的是意思我还不完全明白,你试试这个:select the_day, to_char(the_day, 'day') from(select sysdate + rownum the_day, rownum rn from dual connect by rownum <= 500 --可以改) x 不用考虑的,ORACLE已经帮你弄好了。你只需要按它的格式就行了。 请问是什么样的格式写法,如果直接用to_char(ecm_fltdate,'ww')取到第几周,是按照周日到周六来计算的吧 -- FYI:SQL> SELECT TO_CHAR(SYSDATE,'DAY') MYDATE FROM DUAL;MYDATE---------星期五SQL> SELECT TO_CHAR(SYSDATE,'D') MYDATE FROM DUAL;MYDATE------6SQL> SELECT TO_CHAR(SYSDATE+1,'DAY') MYDATE FROM DUAL;MYDATE---------星期六SQL> SELECT TO_CHAR(SYSDATE+1,'D') MYDATE FROM DUAL;MYDATE------7SQL> SELECT TO_CHAR(SYSDATE+2,'DAY') MYDATE FROM DUAL;MYDATE---------星期日SQL> SELECT TO_CHAR(SYSDATE+2,'D') MYDATE FROM DUAL;MYDATE------1-- MYDATE = TO_DATE('2008-11-01','YYYY-MM-DD')SQL> SELECT DECODE(TO_CHAR(&MYDATE,'D'),'7',&MYDATE-2,'1',&MYDATE-2,&MYDATE) MYDATE FROM DUAL;MYDATE-----------2008-10-30-- MYDATE = TO_DATE('2008-11-02','YYYY-MM-DD')SQL> SELECT DECODE(TO_CHAR(&MYDATE,'D'),'7',&MYDATE-2,'1',&MYDATE-2,&MYDATE) MYDATE FROM DUAL;MYDATE-----------2008-10-31 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 DATEIS 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 CHINA.get_week_daytoday ( in_year VARCHAR2, /*年份*/ in_week VARCHAR2, /*周数*/ in_sun_first NUMBER, /*星期日是第一天还是第一天,0最后天,1第一天*/ year_flag NUMBER /*是否显示年份,0不显示,1显示2位,2显示四位*/) RETURN VARCHAR2IS 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;/给你看两个函数,这是我自己写的,第一个是取查询范围,第二个是取每周从几号到几号你可以参考我第二个函数自己改一下我这个是把周从周一算到周日不过在跨年时,我SQL里有算法的 一个改写rownum的问题 EM和ISQLPLUS链接地址的问题 大整数读写库 关于数据库灭备问题。。多谢! oracle 连接数问题 简单问题求教~~ oracle http server 占用了80端口,但我现在要用iis,怎样才能把80端口解放出来? 以下两个代码段等效吗? 大家讨论一下:出现“ORA-12224: TNS:没有监听器”的问题 mysql的数据迁移到oracle上 WHERE FIELD IS NULL 效率问题?? 求 Oracle I-Learning 的开发资料
select * from table t
where to_char(statDate,'d') = 6;--周五,
/-- 试一下:select to_char(sysdate,'DAY') from dual;
from
(
select sysdate + rownum the_day, rownum rn
from dual
connect by rownum <= 500 --可以改
) x
SQL> SELECT TO_CHAR(SYSDATE,'DAY') MYDATE FROM DUAL;MYDATE
---------
星期五SQL> SELECT TO_CHAR(SYSDATE,'D') MYDATE FROM DUAL;MYDATE
------
6SQL> SELECT TO_CHAR(SYSDATE+1,'DAY') MYDATE FROM DUAL;MYDATE
---------
星期六SQL> SELECT TO_CHAR(SYSDATE+1,'D') MYDATE FROM DUAL;MYDATE
------
7SQL> SELECT TO_CHAR(SYSDATE+2,'DAY') MYDATE FROM DUAL;MYDATE
---------
星期日SQL> SELECT TO_CHAR(SYSDATE+2,'D') MYDATE FROM DUAL;MYDATE
------
1-- MYDATE = TO_DATE('2008-11-01','YYYY-MM-DD')
SQL> SELECT DECODE(TO_CHAR(&MYDATE,'D'),'7',&MYDATE-2,'1',&MYDATE-2,&MYDATE) MYDATE FROM DUAL;MYDATE
-----------
2008-10-30-- MYDATE = TO_DATE('2008-11-02','YYYY-MM-DD')
SQL> SELECT DECODE(TO_CHAR(&MYDATE,'D'),'7',&MYDATE-2,'1',&MYDATE-2,&MYDATE) MYDATE FROM DUAL;MYDATE
-----------
2008-10-31
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 CHINA.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;
/给你看两个函数,这是我自己写的,第一个是取查询范围,第二个是取每周从几号到几号你可以参考我第二个函数自己改一下
我这个是把周从周一算到周日
不过在跨年时,我SQL里有算法的