Oracle日期查找当天，一个星期，一个月的数据

最新推荐文章于 2023-06-08 12:15:39 发布

wanglong20101202

最新推荐文章于 2023-06-08 12:15:39 发布

阅读量678

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签：数据库

本文链接：https://blog.youkuaiyun.com/wanglong20101202/article/details/84340464

Oracle 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了使用SQL创建表、插入数据以及进行日期范围查询、数据聚合等操作，并通过实例展示了如何从表中提取特定时间范围内的数据、计算统计数据以及进行多条件筛选，最后运用SQL进行复杂的数据分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create table A(
aid int primary key,
aname varchar2(20),
adate date,
atext varchar2(50)
);
create sequence a1
start with 1
increment by 1;

create table B(
bid int primary key,
bname varchar2(20),
btype varchar2(10),
bdate date,
b_count int,
aid int 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 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 where trunc(adate) = trunc(sysdate);

--查询一个星期的数据

--一个星期的第一天
select trunc(sysdate,'d') from dual;
--一个星期的最后一天
select next_day(trunc(sysdate,'d'),7) from dual;

--一个星期之内的
select * from A where trunc(adate) >= trunc((select max(adate) from A),'d')
and trunc(adate ) <= next_day(trunc((select max(adate) from A),'d'),7)

--查询一个月的数据
select * from A where to_char(trunc(adate),'yyyymm') = to_char(trunc(sysdate),'yyyymm');

--B表中时间为2012-9的所有数据
select * from A where to_char(trunc(adate),'yyyymm') = to_char(trunc((select max(adate) from A)),'yyyymm');

--查找出B表中type为a，b，c的所有和，和他们所占的比例
select sum(b_count) from B where btype in('a','b','c');

select round((select sum(b_count) from B where btype in('a','b','c'))/(select sum(b_count) from B),4)*100||'%' from B;

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

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