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();

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容,请联系我们,一经查实,本站将立刻删除。
如需转载请保留出处:https://51itzy.com/kjqy/79528.html