mysql 按日、周、月、年统计sql语句整理,实现报表统计可视化 |
您所在的位置:网站首页 › 每日销售报表汇总怎么做 › mysql 按日、周、月、年统计sql语句整理,实现报表统计可视化 |
最近在做报表统计,前端可视化插件用的是Echarts,个人认为是目前免费插件里的翘楚http://echarts.baidu.com/demo.html#pie-doughnut例子演示 现在重点如何把后台数据源搞定,我采用的是mybatis,所以重点是怎么把sql语句写好。 – –按日 SELECT COUNT(*),DATE(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime)='2016' GROUP BY DAY(CreateTime)– –按周 SELECT COUNT(*),WEEK(CreateTime) FROM t_voipchannelrecord WHERE MONTH(CreateTime) = '8' GROUP BY WEEK(CreateTime)–周一到周五每天的统计结果 SELECT COUNT(*),DAYNAME(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime) = '2016' GROUP BY DAYNAME(CreateTime)–统计本周数据 SELECT COUNT(*) FROM t_voipchannelrecord WHERE MONTH(CreateTime) = MONTH(CURDATE()) AND WEEK(CreateTime) = WEEK(CURDATE())–按月统计 SELECT COUNT(*),MONTH(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime) = '2016' GROUP BY MONTH(CreateTime)–统计本月数据 SELECT COUNT(*) FROM t_voipchannelrecord WHERE MONTH(CreateTime) = MONTH(CURDATE()) AND YEAR(CreateTime) = YEAR(CURDATE())–按季统计 SELECT COUNT(*),QUARTER(CreateTime) FROM t_voipchannelrecord WHERE YEAR(CreateTime) = '2016' GROUP BY QUARTER(CreateTime)–按年统计 SELECT COUNT(*),YEAR(CreateTime) FROM t_voipchannelrecord GROUP BY YEAR(CreateTime)–时间段(该段参考:出处) N天内记录 WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段) |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |