mysql查询以年为间隔查询连续的时间
第一种方式:使用mysql的help_topic表进行查询,也可以自己建立一个从小到大一次排序的临时表进行查询
SELECT
DATE_SUB( CURRENT_DATE, INTERVAL help_topic_id YEAR ) dt
FROM
mysql.help_topic
WHERE
help_topic_id BETWEEN 0
AND 6
第二种方式:使用MySQL自定义变量的方式,此方式对mysql版本有要求,同时也需要借助任何一张数据数量大于要查询数据数量的业务表
SELECT
@s := @s + 1 AS `index`,
DATE_FORMAT( DATE_SUB( CURRENT_DATE, INTERVAL @s YEAR ), '%Y-%m-%d' ) AS `date`
FROM
bd_jkb_unit,(
SELECT
@s := - 1
) temp
WHERE
@s < 7
ORDER BY
`date`
mysql查询以月为间隔查询连续的时间(查询方式同上)
第一种方式:
SELECT
DATE_SUB( CURRENT_DATE, INTERVAL help_topic_id MONTH ) dt
FROM
mysql.help_topic
WHERE
help_topic_id BETWEEN 0
AND 6
第二种方式:
SELECT
@s := @s + 1 AS `index`,
DATE_FORMAT( DATE_SUB( CURRENT_DATE, INTERVAL @s MONTH ), '%Y-%m-%d' ) AS `date`
FROM
bd_jkb_unit,(
SELECT
@s := - 1
) temp
WHERE
@s < 7
ORDER BY
`date`
mysql查询以天为间隔查询连续的时间(查询方式同上)
第一种方式:
SELECT
DATE_SUB( CURRENT_DATE, INTERVAL help_topic_id DAY ) dt
FROM
mysql.help_topic
WHERE
help_topic_id BETWEEN 0
AND 6
第二种方式:
SELECT
@s := @s + 1 AS `index`,
DATE_FORMAT( DATE_SUB( CURRENT_DATE, INTERVAL @s DAY ), '%Y-%m-%d' ) AS `date`
FROM
bd_jkb_unit,(
SELECT
@s := - 1
) temp
WHERE
@s < 7
ORDER BY
`date`