Oracle函数trunc、to_date、round、next_day、ratio_to_report的使用

 

create table A(

       aid number primary key,

       aname varchar2(20),

       adate date,

       atext varchar2(50)

);

 

create sequence a1

start with 1

increment by 1;

 

create table B(

       bid number primary key,

       bname varchar2(20),

       btype varchar2(10),

       bdate date,

       bcount number,

       aid number references A(aid)

);

 

create sequence b1

start with 1

increment by 1;

 

 

insert into A values(a1.nextval,'ly0',(to_date('2012-10-26','yyyy-MM-DD')),'aaa0');

insert into A values(a1.nextval,'ly1',(to_date('2012-10-25','yyyy-MM-DD')),'aaa1');

insert into A values(a1.nextval,'ly2',(to_date('2012-10-24','yyyy-MM-DD')),'aaa2');

insert into A values(a1.nextval,'ly3',(to_date('2012-10-23','yyyy-MM-DD')),'aaa3');

insert into A values(a1.nextval,'ly4',(to_date('2012-10-22','yyyy-MM-DD')),'aaa4');

insert into A values(a1.nextval,'ly5',(to_date('2012-10-21','yyyy-MM-DD')),'aaa5');

insert into A values(a1.nextval,'ly6',(to_date('2012-10-20','yyyy-MM-DD')),'aaa6');

insert into A values(a1.nextval,'ly7',(to_date('2012-10-19','yyyy-MM-DD')),'aaa7');

insert into A values(a1.nextval,'ly8',(to_date('2012-10-18','yyyy-MM-DD')),'aaa8');

insert into A values(a1.nextval,'ly9',(to_date('2012-10-17','yyyy-MM-DD')),'aaa9');

insert into A values(a1.nextval,'ly10',(to_date('2012-10-16','yyyy-MM-DD')),'aaa10');

insert into A values(a1.nextval,'ly11',(to_date('2012-10-16 22:24:12','yyyy-MM-DD hh24:mi:ss')),'aaa11');

 

insert into B values(b1.nextval,'lyB0','a',(to_date('2012-10-26','yyyy-MM-DD')),8,1);

insert into B values(b1.nextval,'lyB1','c',(to_date('2012-10-25','yyyy-MM-DD')),8,1);

insert into B values(b1.nextval,'lyB2','b',(to_date('2012-10-24','yyyy-MM-DD')),8,2);

insert into B values(b1.nextval,'lyB3','a',(to_date('2012-10-23','yyyy-MM-DD')),8,1);

insert into B values(b1.nextval,'lyB4','b',(to_date('2012-10-22','yyyy-MM-DD')),8,3);

insert into B values(b1.nextval,'lyB5','b',(to_date('2012-10-21','yyyy-MM-DD')),8,5);

insert into B values(b1.nextval,'lyB6','a',(to_date('2012-10-20','yyyy-MM-DD')),8,1);

insert into B values(b1.nextval,'lyB7','d',(to_date('2012-10-19','yyyy-MM-DD')),8,2);

insert into B values(b1.nextval,'lyB8','e',(to_date('2012-10-18','yyyy-MM-DD')),8,4);

insert into B values(b1.nextval,'lyB9','c',(to_date('2012-10-17','yyyy-MM-DD')),8,4);

insert into B values(b1.nextval,'lyB10','a',(to_date('2012-10-16','yyyy-MM-DD')),8,2);

insert into B values(b1.nextval,'lyB11','c',(to_date('2012-9-16','yyyy-MM-DD')),8,1);

insert into B values(b1.nextval,'lyB12','a',(to_date('2012-9-12','yyyy-MM-DD')),8,2);

insert into B values(b1.nextval,'lyB13','d',(to_date('2012-9-13','yyyy-MM-DD')),8,4);

insert into B values(b1.nextval,'lyB14','a',(to_date('2012-9-11','yyyy-MM-DD')),8,3);

commit;

 

select * from A;

 

select * from B;

 

 

--查询当天的所有数据

select * from A where trunc(adate) = trunc(sysdate);

 

--一个星期的第一天

select trunc(sysdate,'d') from dual;

 

--一个星期的最后一天

select next_day(trunc(sysdate,'d'),7) from dual;

 

--最近一个星期之内的

select * from a 

where adate>=trunc(to_date('2012-11-15','yyyy-MM-DD'),'d') 

and adate<=next_day(trunc(to_date('2012-11-15','yyyy-MM-DD'),'d'),7);

 

 

--查询一个月的数据

select * from A where trunc(adate,'mm') = trunc(to_date('2012-10-15','yyyy-MM-DD'),'mm')

 

 

--B表中时间为2012-9的所有数据

select * from B where trunc(bdate,'mm')=trunc(to_date('2012-09','yyyy-MM'),'mm');

 

 

--查找出B表中type为a，b，c的所有和，和他们所占的比例

select t1.*,t2.*,round(t1.typecount/t2.allcount,4)*100||'%' from 

(select btype,sum(bcount) as typecount from b where btype in('a','b','c') group by btype) t1,

(select sum(bcount) as allcount from b where btype in('a','b','c')) t2 

 

--查询每一行占总的比例

--方法一：

select b.*,t1.allcount,round(b.bcount/allcount,4)*100||'%' from 

(select sum(bcount) as allcount from B) t1,b;

--方法二：

select bid,bname,btype,bdate,bcount,aid,ratio_to_report(bcount) over() as scale from B;