按周统计

一、条件表达式
select ename,deptno,sal,
 decode(deptno,
  10,sal*1.2,
  20,sal*1.1,sal)salary from emp

select ename,sal,case deptno
 when 10 then sal*1.2
 when 20 then sal*1.1
 else sal end salary
from emp;

select ename,sal,case
 when sal<2000 then sal*1.2
 when sal<3000 then sal*1.1
 else sal end salary
from emp;

select case
 when extract(day from sysdate)=1 then add_months(last_day(sysdate),-2)+1
 else add_months(last_day(sysdate),-1)+1 end v_date
from dual;
 
declare
       v_date date;
begin
     select add_months(last_day(sysdate),-1)+1 into v_date from dual;
     if extract(day from sysdate)=1 then
        select add_months(last_day(sysdate),-2)+1 into v_date from dual;
     end if;
     dbms_output.put_line(extract(day from sysdate));
     dbms_output.put_line(v_date);
end;

二、时间日期函数

1、Sysdate 当前日期和时间
SQL> Select sysdate from dual;

2、Last_day 本月最后一天
SQL> Select last_day(sysdate) from dual;

3、Add_months(d,n) 当前日期d后推n个月
SQL> Select add_months(sysdate,2) from dual;

上月末天
select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual

上月今天
select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual

上月首天
select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual

4、Months_between(f,s) 日期f和s间相差月数
SQL> select months_between(sysdate,to_date('2005-11-12','yyyy-mm-dd'))from dual;

5、NEXT_DAY(d, day_of_week)
返回由"day_of_week"命名的，在变量"d"指定的日期之后的第一个工作日的日期。参数"day_of_week"必须为该星期中的某一天。
SQL> SELECT next_day(sysdate,1) FROM dual;

6、extract()找出日期或间隔值的字段值
select extract(year from sysdate) Year from dual;
select extract(month from sysdate) Month from dual;
select extract(day from sysdate) Day from dual;

select case
 when extract(day from sysdate)=1 then add_months(last_day(sysdate),-2)+1
 else add_months(last_day(sysdate),-1)+1 end v_date
from dual;

select to_char(sysdate,'YYYY') from dual;
select to_char(sysdate,'MM') from dual;
select to_char(sysdate,'DD') from dual;
select to_char(sysdate,'day') from dual;

7、找到某月中所有周五的具体日期
select to_char(t.d,'YY-MM-DD'),to_char(t.d,'day') from (
select trunc(sysdate, 'MM')+rownum-1 as d
from dba_objects
where rownum < 32) t
where to_char(t.d, 'MM') = to_char(sysdate, 'MM')
and trim(to_char(t.d, 'Day')) = '星期五'
如果把where to_char(t.d, 'MM')=to_char(sysdate,'MM')改成sysdate-90，即为查找当前月份的前三个月中的每周五的日期

8、trunc函数
select trunc(sysdate,'yy') from dual--本年第一天
select trunc(sysdate,'mm') from dual--本月第一天
select trunc(sysdate,'dd') from dual--当天

select trunc(4665466.469964,2) from dual--保留两位小数
4665466.46
select trunc(4665466.469964,-1) from dual
4665460
select round(4665466.469964,2) from dual--保留两位小数(四舍五入)
4665466.47
select round(4665466.469964,-1) from dual
4665470

 

三、按周统计
select to_char(date_time,'yyyymm')年月,
       '第'||to_char(date_time,'iw')||'周' 周,
       count(*),sum(amount)
from temp_week
group by to_char(date_time,'yyyymm'),
         to_char(date_time,'iw')
order by 2

年月 周 COUNT(*)  SUM(AMOUNT)
200805 第18周 4   214
200805 第19周  2   124

WW的算法为每年1月1日为第一周开始，date+6为每一周结尾

iw的算法为星期一至星期日算一周，且每年的第一个星期一为第一周

W的算法为每月1日为第一周开始，date+6为每一周结尾

按月统计
select to_char(sysdate,'mm') from dual group by to_char(sysdate,'mm')

按季统计
select to_char(sysdate,'q') from dual group by to_char(sysdate,'q')

按年统计
select to_char(sysdate,'yyyy') from dual group by to_char(sysdate,'yyyy')

一年中所有的周
drop table temp_date;
create table temp_date
(date_time date,
 week_time varchar2(10),
 week_no number(2)
 );
commit;

declare
  x number(3);
begin
  x:=0;
 loop
  insert into temp_date
  select to_char(sysdate-to_char(sysdate-to_date(20080101,'yyyy/mm/dd'))+x,'yyyy/mm/dd') date_time,
         to_char(sysdate-to_char(sysdate-to_date(20080101,'yyyy/mm/dd'))+x,'day') week_time,
         to_char(sysdate-to_char(sysdate-to_date(20080101,'yyyy/mm/dd'))+x,'iw') week_no
  from dual;
  x:=x+1;
 exit when x=366;
 end loop;
end;
/
select * from temp_date;

select week_no,
       max(decode(week_time,'星期一',date_time)) 星期一,
       max(decode(week_time,'星期日',date_time)) 星期日
from temp_date
group by week_no
order by week_no

 

create table temp_week
(
 art_no number(6),
 amount number(9,2),
 date_time date
 )

insert into temp_week values
(100012,53,to_date('20080522','yyyy-mm-dd'))

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17012874/viewspace-693793/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17012874/viewspace-693793/