sql日期函数统计日月年订单数

场景:汇集日月年的订单数,分别在mysql和oracle数据库实现相同的效果

示例1:

--创建mysql测试表 
drop table test.test;
create table test.test
(
id bigint(20) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL COMMENT '名称',
c_date date ,
PRIMARY KEY (`id`)
) CHARSET = utf8mb4 COMMENT = '测试表';

insert into test.test(name,c_date) values ('one',date_add(now(), interval -1 day));
insert into test.test(name,c_date) values ('two',date_add(now(), interval -2 day));
insert into test.test(name,c_date) values ('three',date_add(now(), interval -3 day));
insert into test.test(name,c_date) values ('four',date_add(now(), interval -3 day));

select * from test.test;
id	name	c_date
1	one	2024-05-19
2	two	2024-05-18
3	three	2024-05-17
4	four	2024-05-17

--昨日
select date_format(date_add(current_timestamp(), interval -1 day),'%Y%m%d') ;
20240520
--当日
select date_format(current_timestamp(),'%Y%m%d') ;
20240521
--本周
select week(current_timestamp()) from dual;
20
--本月
select date_format(current_timestamp(),'%Y%m') ;
202405
--本年
select date_format(current_timestamp(),'%Y') ;
2024

--使用sql实现统计昨日当日当周当月当年的数据量
select 
'昨日' as  c_type
,count(1) as cnt  
from  test.test
where date_format(c_date,'%Y%m%d') = date_format(date_add(current_timestamp(), interval -1 day),'%Y%m%d')

union all
select 
'当日' as  c_type
,count(1) as cnt  
from  test.test
where date_format(c_date,'%Y%m%d') = date_format(current_timestamp(),'%Y%m%d')

union all
select 
'本周' as  c_type
,count(1) as cnt  
from  test.test
where c_date between concat(date_format( date_sub(current_timestamp(),interval weekday(current_timestamp()) day),'%Y-%m-%d'),' 00:00:00') 
	and concat(date_format( date_sub(current_timestamp(),interval weekday(current_timestamp())- 6 day),'%Y-%m-%d'),' 23:59:59') 

union all
select 
'本月' as  c_type
,count(1) as cnt  
from  test.test
where date_format(c_date,'%Y%m') = date_format(current_timestamp(),'%Y%m')

union all
select 
'本年' as  c_type
,count(1) as cnt  
from  test.test
where date_format(c_date,'%Y') = date_format(current_timestamp(),'%Y')

c_type	cnt
昨日	0
当日	0
本周	0
本月	4
本年	4

示例2:

--创建oracle测试表 
drop table test.test;
create table test.test
(
id int NOT NULL,
name varchar2(50) DEFAULT NULL ,
c_date date ,
CONSTRAINT "pk_id" PRIMARY KEY (id)
) ;

insert into test.test
select '1','one', sysdate - 1  FROM dual
 union all
select '2','two', sysdate - 2 FROM dual
 union all
select '3','three', sysdate - 3 FROM dual
 union all
select '4','four', sysdate - 3 FROM dual

SELECT * FROM  test.test
ID	NAME	C_DATE
1	one	2024-05-19 18:02:03.000
2	two	2024-05-18 18:02:03.000
3	three	2024-05-17 18:02:03.000
4	four	2024-05-17 18:02:03.000

--昨日
select TO_CHAR(TRUNC(sysdate-1), 'yyyymmdd') from dual;
20240520
--当日
select TO_CHAR(TRUNC(sysdate), 'yyyymmdd') from dual;
20240521
--本周
select TO_CHAR(SYSDATE,'IW')  from dual;
21
--本月
select TO_CHAR(TRUNC(sysdate), 'yyyymm') from dual;
202405
--本年
select TO_CHAR(TRUNC(sysdate), 'yyyy') from dual;
2024

--使用sql实现统计昨日当日当周当月当年的数据量
select 
'昨日' as  c_type
,count(1) as cnt  
from  test.test
where c_date between TRUNC(sysdate - 1) and TRUNC(sysdate - 1) + 0.99999

union all
select 
'当日' as  c_type
,count(1) as cnt  
from  test.test
where c_date between TRUNC(sysdate) and TRUNC(sysdate) + 0.99999

union all
select 
'本周' as  c_type
,count(1) as cnt  
from  test.test
where c_date between TRUNC(SYSDATE ,'iw') and TRUNC(SYSDATE ,'iw') + 6 + 0.99999

union all
select 
'本月' as  c_type
,count(1) as cnt  
from  test.test
where TO_CHAR(c_date,'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM')

union all
select 
'本年' as  c_type
,count(1) as cnt  
from  test.test
where TO_CHAR(c_date,'YYYY') = TO_CHAR(SYSDATE, 'YYYY')

C_TYPE	CNT
昨日	0
当日	0
本周	0
本月	4
本年	4