按年、月、日统计数据sql写法

1、 按年月日分组统计数据sql写法

一、 mysql的写法

最近遇到安年月日统计的问题，短时间（几秒钟）还没头绪，所以总结一下，记录一下。
----------写在前面的话

###### 
1.先看数据库，我随便找了一个有时间的数据库，没仔细去琢磨，将就用。

2.我们先认识几个函数

uploadtime：2019-12-08 14:49:30
year(uploadtime) 获取时间字段中的年，结果为2019
month(uploadtime) 获取时间字段中的月，结果为12
day(uploadtime) 获取时间字段中的日，结果为08

3.按年分类统计：

select year(uploadtime) as yy,count(*),sum(filetypeid) from sys_base_fileindex GROUP BY yy

4.按月分类统计：

select year(uploadtime) as yy, month(uploadtime) as mm,count(*),sum(filetypeid) from sys_base_fileindex GROUP BY yy,mm

5.按每天统计数据

select year(uploadtime) as yy, month(uploadtime) as mm,day(uploadtime) as dd ,count(*),sum(filetypeid) from sys_base_fileindex GROUP BY yy,mm,dd

总结：
这里有一个指点，group by的多个字段排序：
GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里。
GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里
GROUP BY X, Y，Z意思是将所有具有相同X字段值和Y字段值和Z字段值的记录放到一个分组里

二、oracle的写法

按年分组：

select 
  to_char(a.create_date,'yyyy') as date_year,
  count(*) as num
from sys_bug a
left join sys_user u on u.id = a.create_by 
left join sys_office o on o.id = u.office_id
group by to_char(a.create_date,'yyyy')

按月分组

select 
  to_char(a.create_date,'yyyy-MM') as date_month,
  count(*) as num
from sys_bug a
left join sys_user u on u.id = a.create_by 
left join sys_office o on o.id = u.office_id
group by o.id,o.name,to_char(a.create_date,'yyyy-MM')

按周分组：

//这里的ww是获取该日期的在年中的第几周
select 
  to_char(a.create_date,'ww') as date_week,
  count(*) as num
from sys_bug a
left join sys_user u on u.id = a.create_by 
left join sys_office o on o.id = u.office_id
group by o.id,o.name,to_char(a.create_date,'ww')

按季度分组：

//在oracle中q为时间的季度如
select 
  to_char(a.create_date,'q') AS date_seasons,
  count(*)
from t_dzjg_news a
left join sys_user u on u.id = a.create_by 
left join sys_office o on o.id = u.office_id
group by o.id, to_char(a.create_date,'q')

按天分组：

select 
  to_char(a.create_date,'yyyy-MM-dd') AS date_day,
  count(*)
from t_dzjg_news a
left join sys_user u on u.id = a.create_by 
left join sys_office o on o.id = u.office_id
group by o.id, to_char(a.create_date,'yyyy-MM-dd')

总结：
sysdate：当前日期和时间；
to_char()：将数值型或日期型转换为字符型；
to_date()：日期转换函数，例：to_date(‘2017-09-27 10:00:00’, ‘yyyy-mm-dd hh24:mi:ss’)；
last_day(sysdate)：返回日期所在月的最后一天；
trunc(number[,decimals])：指定元素格式截去一部分日期值；
add_months(D,N)：返回日期D加N月后对应的日期时间，N为正时则表示D之后；N为负时则表示为D之前；N为小数则会自动先删除小数部分，而用整数部分;
decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)：根据条件返回相应的值；
ceil(n)：取大于等于数值n的最小整数；
floor(n)：取小于等于数值n的最大整数；
dbtimezone()：返回时区；

2、mysql 数据库查询今天、昨天、本月、上月、上季度的数据

今天： select * from 表名 where to_days(时间字段名) = to_days(now());
昨天： SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
七天：SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
近30天：SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
本月：SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, "%Y%m" ) = DATE_FORMAT( CURDATE( ) , "%Y%m" )
上一月：SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , "%Y%m" ) , date_format( 时间字段名, "%Y%m" ) ) =1
查询本季度：select * from ht_invoice_information where QUARTER(create_date)=QUARTER(now());
查询上一季度：select * from ht_invoice_information where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
查询本年：select * from ht_invoice_information where YEAR(create_date)=YEAR(NOW());
查询上一年：select * from ht_invoice_information where year(create_date)=year(date_sub(now(),interval 1 year));
查询当前这周:SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,"%Y-%m-%d")) = YEARWEEK(now());
查询上周: SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,"%Y-%m-%d")) = YEARWEEK(now())-1;
查询当前月份：select name,submittime from enterprise where date_format(submittime,"%Y-%m")=date_format(now(),"%Y-%m")
查询距当前6个月的数据：select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();