# 按周统计

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;

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)

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')) = '星期五'

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

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'))

• 博文量
80
• 访问量
75819