SELECT
(((
SELECT
( curdate() - INTERVAL 30 YEAR )) - INTERVAL 1 YEAR
) + INTERVAL ( cast( `mysql`.`help_topic`.`help_topic_id` AS signed ) + 1 ) YEAR
) AS `current_week`
FROM
`mysql`.`help_topic`
WHERE
(
`mysql`.`help_topic`.`help_topic_id` < 30)
ORDER BY
`mysql`.`help_topic`.`help_topic_id`
忘了原sql是从哪里找到的了,给改造了一下,上边的是构造当前时间向前推30年的连续年,需要连续月就把“YEAR”改成“MONTH”即可得到连续月;同理,需要连续天则改成“DAY”。
需要构造连续多少条时间就把30改成对应需要的数字,以此作为基表关联业务表即可统计连续时间段业务情况。
在mapper.xml中写自己的逻辑即可,仅供参考
<select id="select" resultMap="Vo">
select d.date,count(t.id) count
form
(SELECT
(((
SELECT
( curdate() - INTERVAL #{连续时间长度}
<if test="判断传入参数">
YEAR
</if>
)) - INTERVAL 1
<if test="判断传入参数">
YEAR
</if>
) + INTERVAL ( cast( `mysql`.`help_topic`.`help_topic_id` AS signed ) + 1 )
<if test="判断传入参数">
YEAR
</if>
) AS `current_week`
FROM
`mysql`.`help_topic`
WHERE
(
`mysql`.`help_topic`.`help_topic_id` < #{连续时间长度} )
ORDER BY
`mysql`.`help_topic`.`help_topic_id`) d
left join (
/** 具体的业务表 */
)T on T.date = d.date
</select>
如果按非当前时间计算还需要考虑到日期格式转换