mysql 查询 系统时间表 作为主表 的子查询

1. 查询最近7天的时间列表 子表

//查询最近7天的时间列表 子表 sql
SELECT @s :=@s + 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := -1) temp
WHERE @s < 6
ORDER BY `date`

2. 查询一天24小时的时间列表 子表

//查询一天24小时的时间列表 子表 sql
SELECT @d := @d + 1 `hour`
FROM mysql.help_topic,(SELECT @d := -1) temp
WHERE @d < 23
ORDER BY `hour`

3. 查询本周的时间列表 子表

//查询本周的时间列表 子表 sql
SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE, INTERVAL @a DAY)) AS `date`
FROM mysql.help_topic,(SELECT @a := 0) temp
WHERE @a < 6 - WEEKDAY(CURRENT_DATE)
UNION
SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := WEEKDAY(CURRENT_DATE) + 1) temp
WHERE @s > 0
ORDER BY `date`

4. 查询本月的时间列表 子表

//查询本月的时间列表 子表 sql
SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE, INTERVAL @a DAY)) AS `date`
FROM mysql.help_topic,(SELECT @a := 0) temp
WHERE @a < DAY(LAST_DAY(CURRENT_DATE)) - DAY(CURRENT_DATE)
UNION
SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := day(CURRENT_DATE)) temp
WHERE @s > 0
ORDER BY `date`