关于mysql报表统计的按月按周按季度

最新推荐文章于 2023-06-15 16:18:28 发布

young尊

最新推荐文章于 2023-06-15 16:18:28 发布

阅读量868

点赞数 1

分类专栏： java

本文链接：https://blog.csdn.net/qq_43543676/article/details/88968269

版权

java 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

关于按月按季度按周统计报表

按季度

SELECT
t2.name,
sum( IF(QUARTER(t1.GMT_CREATE)='1',t2.APPLY_NUM,0) ) "Q1th",
sum( IF(QUARTER(t1.GMT_CREATE)='2',t2.APPLY_NUM,0) ) "Q2nd",
sum( IF(QUARTER(t1.GMT_CREATE)='3',t2.APPLY_NUM,0) ) "Q3rd",
sum( IF(QUARTER(t1.GMT_CREATE)='4',t2.APPLY_NUM,0) ) "Q4th"

FROM
	oisms_apply_record t1
LEFT JOIN oisms_apply_recordline t2 ON t1.ID = t2.MAIN_ID
where YEAR(t1.GMT_CREATE) = YEAR(CURDATE())
group by 
 QUARTER(t1.GMT_CREATE),
 t2.code

按月份

SELECT
t2.code,
t2.name,
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%b')='Jan',t2.APPLY_NUM,0) ) "Jan",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%b')='Feb',t2.APPLY_NUM,0) ) "Feb",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%b')='Mar',t2.APPLY_NUM,0) ) "Mar",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%b')='Apr',t2.APPLY_NUM,0) ) "Apr",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%b')='May',t2.APPLY_NUM,0) ) "May",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%b')='Jun',t2.APPLY_NUM,0) ) "Jun",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%b')='Jul',t2.APPLY_NUM,0) ) "Jul",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%b')='Aug',t2.APPLY_NUM,0) ) "Aug",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%b')='Sep',t2.APPLY_NUM,0) ) "Sep",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%b')='Oct',t2.APPLY_NUM,0) ) "Oct",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%b')='Nov',t2.APPLY_NUM,0) ) "Nov",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%b')='Dec',t2.APPLY_NUM,0) ) "Dec"
FROM
	oisms_apply_record t1
LEFT jOIN oisms_apply_recordline t2 ON t1.ID = t2.MAIN_ID
where YEAR(t1.GMT_CREATE) = YEAR(CURDATE())
GROUP BY t2.code

按周

		SELECT
t2.code,
t2.name,
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%a')='Mon',t2.APPLY_NUM,0) ) "Mon",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%a')='Tue',t2.APPLY_NUM,0) ) "Tue",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%a')='Wed',t2.APPLY_NUM,0) ) "Wed",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%a')='Thu',t2.APPLY_NUM,0) ) "Thu",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%a')='Fri',t2.APPLY_NUM,0) ) "Fri",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%a')='Sat',t2.APPLY_NUM,0) ) "Sat",
sum( IF(DATE_FORMAT(t1.GMT_CREATE, '%a')='Sun',t2.APPLY_NUM,0) ) "Sun"
FROM
	oisms_apply_record t1
LEFT jOIN oisms_apply_recordline t2 ON t1.ID = t2.MAIN_ID
where WEEK(t1.GMT_CREATE) = WEEK(CURDATE()) and MONTH(t1.GMT_CREATE) = MONTH(CURDATE())
GROUP BY t2.code

数据库用测试数据查询出来的结果
在这里插入图片描述