MySQL 的 YEARWEEK 是获取年份和周数的一个函数,函数形式为 YEARWEEK(
date
[,mode
])
例如 2010-3-14 ,礼拜天
SELECT YEARWEEK('2010-3-14') 返回 11
SELECT YEARWEEK('2010-3-14',1) 返回 10
其中第二个参数是 mode ,具体指的意思如下:
Mode | First day of week | Range | Week 1 is the first week … |
0 | Sunday | 0-53 | with a Sunday in this year |
1 | Monday | 0-53 | with more than 3 days this year |
2 | Sunday | 1-53 | with a Sunday in this year |
3 | Monday | 1-53 | with more than 3 days this year |
4 | Sunday | 0-53 | with more than 3 days this year |
5 | Monday | 0-53 | with a Monday in this year |
6 | Sunday | 1-53 | with more than 3 days this year |
7 | Monday | 1-53 | with a Monday in this year |
例子:
1、查询本周的数据:
<select id="getAgentAuditCount" parameterType="java.util.Map" resultType="java.lang.Long"> select count(*) from hzf_agent_info where 1=1 <if test="auditStatus != null" > and audit_status = #{auditStatus} </if> <if test="timeStamp == 7" > and YEARWEEK(date_format(register_time,'%Y-%m-%d'),1) = YEARWEEK(now(),1) </if> <if test="timeStamp == 1" > and to_days(register_time) = to_days(now()) </if> </select>
2、获取上周的数据:
<select id="getBridgeInfoByWeek" parameterType="String" resultMap="BaseResultMap"> select * from bridge_info WHERE apply_user = #{applayUser} and status = 4 and YEARWEEK(date_format(apply_date,'%Y-%m-%d'),1) = YEARWEEK(now(),1)-1 </select>