mysql语言中取季度_Mybatis中按照年、季度、月、日查询

一.参考链接：

https://blog.csdn.net/beidaol/article/details/86079157(MyBatis按今天、本周、本月过滤查询)

https://blog.csdn.net/qq_41456723/article/details/100805761(MyBatis(MySQL)-- 年|月|周时间范围内查询统计)

https://blog.csdn.net/u010543785/article/details/52354957(mysql 按日、周、月、年统计sql语句整理，实现报表统计可视化)

https://blog.csdn.net/qq_39549434/article/details/78632984(使用MyBatis对项目中的统计功能进行处理的查询语句优化)

https://www.cnblogs.com/huanghuanghui/p/9997041.html(mybatis报表，动态列与查询参数+行列转换)

https://blog.csdn.net/db_guy/article/details/78330529(mysql分别按照季，月，周分别统计数据：concat()函数)

https://blog.csdn.net/ydk888888/article/details/77965501(Mysql,Mybatis做时间过滤报表:日周月统计)

https://www.cnblogs.com/caoyc/p/5574948.html(Mybatis choose (when, otherwise)标签)

https://www.w3school.com.cn/sql/func_date_format.asp(MySQL DATE_FORMAT() 函数)

1.if条件查询

choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则 choose 结束。

/**

* 统计信息

* @param timeType 时间范围: 1 ==> 年; 2 ==> 月; 3 ==> 周;

* @return

*/

SELECT

sum(bd.cog_id = '4') AS total,

sum(bd.proc_state = '1') AS ban,

sum(bd.proc_state = '2') AS disapper,

sum(bd.label_state = '0') AS new

FROM broadcast bd

DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= DATE(last_appeartime);

DATE_SUB(CURDATE(), INTERVAL 1 MONTH ) <= DATE(last_appeartime);

DATE_SUB(CURDATE(), INTERVAL 1 WEEK ) <= DATE(last_appeartime);

声明：classType------0：今日，1：周，2：月，3：自定义，4：最近七天

AND DATE_FORMAT(s.create_time,'%Y%u') = DATE_FORMAT(CURDATE( ),'%Y%u')

AND DATE_FORMAT(s.create_time,'%Y%m') = DATE_FORMAT(CURDATE( ),'%Y%m')

AND Date(s.create_time) between #{beginTime,jdbcType=VARCHAR} and CURDATE()

AND Date(s.create_time) <= #{endTime,jdbcType=VARCHAR}

AND Date(s.create_time) between #{beginTime,jdbcType=VARCHAR} and #{endTime,jdbcType=VARCHAR}

AND Date(s.create_time) = CURDATE()

AND date(s.create_time) between date_sub(curdate(), INTERVAL 6 DAY) and curdate()

AND Date(s.create_time) = CURDATE()

AND Year(create_time) >=#{params.beginTime}/*按照年度查询*/

AND Year(create_time) <= #{params.endTime}/**结束年度查询*/

AND concat(Year(create_time),quarter(create_time)) >= concat(Year(#{params.beginTime}),day(#{params.beginTime}))/**按照季度查询*/

AND concat(Year(create_time),quarter(create_time)) <= concat(Year(#{params.endTime}),day(#{params.endTime})) /**结束季度查询*/

AND date_format(create_time,'%y%m') >=date_format(#{params.beginTime},'%y%m')/**按照月份查询*/

AND date_format(create_time,'%y%m') <= date_format(#{params.endTime},'%y%m') /**结束月份查询*/

AND date_format(create_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')

AND date_format(create_time,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')

2.choose条件查询

AND Year(create_time) >=#{params.beginTime}

AND Year(create_time) <= #{params.endTime}

AND concat(Year(create_time),quarter(create_time)) >= concat(Year(#{params.beginTime}),day(#{params.beginTime}))

AND concat(Year(create_time),quarter(create_time)) <= concat(Year(#{params.endTime}),day(#{params.endTime}))

AND date_format(create_time,'%y%m') >=date_format(#{params.beginTime},'%y%m')

AND date_format(create_time,'%y%m') <= date_format(#{params.endTime},'%y%m')

AND date_format(create_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')

AND date_format(create_time,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')