方法1:使用to_char函数
select sum(sal) ,
to_char(HIREDATE,‘yyyy‘)||‘:‘||to_char(HIREDATE,‘IW‘) week_sn
from scott.emp
group by to_char(HIREDATE,‘yyyy‘)||‘:‘||to_char(HIREDATE,‘IW‘);
格式‘IW‘返回当前日期是当年的第几周
方法2:使用next_day()函数
select sum(sal) ,
NEXT_DAY(trunc(HIREDATE),‘星期一‘)-7 weekstart,
NEXT_DAY(trunc(HIREDATE),‘星期一‘) weekend
from scott.emp
group by NEXT_DAY(trunc(HIREDATE),‘星期一‘);
上面这个例子是在中文字符集下使用,如果在英文字符集下则使用:
select sum(sal) ,
NEXT_DAY(trunc(HIREDATE),‘Monday‘)-7 weekstart,
NEXT_DAY(trunc(HIREDATE),‘Monday‘) weekend
from scott.emp
group by NEXT_DAY(trunc(HIREDATE),‘Monday‘);
两种方法比较,第二种方法能够方便的给出周开始和周结束日期,更方便些。
联系客服