SQL查询近31天、半年、一年、三年的数据的sql 没有数据会在保留日期后面加0 保证日期完整呈现

前言：我们在公司写接口的时候经常会用图标分析的接口要求返回最近一个月（并不是当月的数据
从查询的日期前推30天的数据）放回数据并不难但是，需要带着日期一起放回，每一天的日期都要返回给前端。用java写就要用到麻烦的循环操作，但是有方便的SQL函数帮助我们建立一个日期的虚拟边，只要自己查询的数据与关联表关联起来就能达到目的。

最近31天的虚拟表
SELECT ADDDATE(CURDATE(), INTERVAL @d DAY) AS TheDate, @d := @d - 1 DAY FROM
t_power, (SELECT @d := 0) temp WHERE ADDDATE(CURDATE(), INTERVAL @d DAY) >=
DATE_ADD(CURDATE(), INTERVAL - 1 MONTH)

修改算法：可以查询到146条从截止到今天的数据，但是为什么只能查询到146条就不清楚了，但是能满足开发需要。
SELECT DATE_FORMAT(TheDate, '%m/%d') AS TheDate FROM (SELECT
ADDDATE(CURDATE(), INTERVAL @d DAY ) AS TheDate, @d := @d - 1 DAY FROM t_power,
(SELECT @d := 0) temp) test LIMIT 0,150
也可以将CURDATE()修改成固定日期不过格式需要统一：将CURDATE()修改成 '2018-08-23'（记得要单引号要加上）

需求变更：当与mybatis整合和后需要查询出前端传来指定范围的数据（将CURDATE()修改成 #{endTime}
这样数据可以根据前端传的日期进行分段显示要注意的是传回来的日期格式： 2018-08-23 月份和天不能简写成单个数字 2018-8-23
月份为单个数字了是错误的）
SELECT ADDDATE(#{endTime}, INTERVAL @d DAY ) AS TheDate, @d := @d - 1 DAY FROM
t_power, (SELECT @d := 0) temp ) test WHERE TheDate >= #{startTime}

最近6个月的虚拟表
SELECT DATE_FORMAT(TheDate, '%Y-%m') AS TheDate FROM (SELECT
ADDDATE(CURDATE(), INTERVAL @d MONTH) AS TheDate, @d := @d - 1 MONTH FROM
t_power, (SELECT @d := 0) temp) test LIMIT 0, 6

最近一年的虚拟表

SELECT DATE_FORMAT(TheDate, '%Y-%m') AS TheDate FROM (SELECT
ADDDATE(CURDATE(), INTERVAL @d MONTH) AS TheDate, @d := @d - 1 MONTH FROM
t_power, (SELECT @d := 0) temp) test LIMIT 0,12
上面的时间虚拟表（将上面的时间表设置为：s）与自己查询出来带有日期的表（设为：a（时间date字段））关联起来：

select * from s left join a on s.TheDate=a.date ORDER BY s.TheDate

这样就能关联形成一个带有你想要日期的表

eg:
SELECT IFNULL(a.alarmcount, 0) AS alarmcount, #{moduleID} as moduleid,
IFNULL(a.falsecount, 0) AS falsecount, IFNULL(a.handlecount, 0) AS handlecount,
DATE_FORMAT(s.TheDate, '%m.%d') AS DATE FROM ( SELECT ADDDATE(CURDATE()-1,
INTERVAL @d DAY) AS TheDate, @d := @d - 1 DAY FROM t_power, (SELECT @d := 0)
temp WHERE ADDDATE(CURDATE(), INTERVAL @d DAY) >= DATE_ADD(CURDATE(), INTERVAL
- 1 MONTH) ) AS s LEFT JOIN ( SELECT DATE(t1.`addtime`) AS date, COUNT(t1.`id`)
AS alarmcount, SUM( CASE WHEN t1.`handlestate` = 2 THEN 1 ELSE 0 END ) AS
handlecount, SUM( CASE WHEN t1.`handleresult` = 1 THEN 1 ELSE 0 END ) AS
falsecount FROM t_dev_alarm t1 INNER JOIN t_device_all t2 ON t1.`deviceid` =
t2.`id` AND t1.`moduleid` =#{moduleID} AND t2.`moduleid` =#{moduleID} AND
t2.`isdelete` = 0 INNER JOIN t_device_group_relation t3 ON t3.`deviceid` =
t2.`id` AND t3.`isdelete` = 0 AND t3.`moduleid` = #{moduleID} INNER JOIN
t_device_group t4 ON t4.`id` = t3.`groupid` AND t4.`isdelete` = 0 AND
t4.`moduleid` = #{moduleID} INNER JOIN t_user_dgroup_relation t5 ON
t5.`devicegroupid` = t4.`id` AND t5.`isdelete` = 0 INNER JOIN t_user t6 ON
t6.`id` = t5.`userid` AND t6.`isdelete` = 0 AND t6.`id` = #{userID} GROUP BY
DATE_FORMAT(t1.addtime, '%Y-%m-%d') ) a ON a.date = s.TheDate ORDER BY s.TheDate

一年中的12个月也可以用java来写
List<DevStateVO> sonlist = waterImmersionMapper.selectDevState(Time, moduleID,
userID); List<DevStateVO> father = new ArrayList<>(); int[] i = { 1, 2, 3, 4,
5, 6, 7, 8, 9, 10, 11, 12 }; for (int j = 0; j < i.length; j++) { DevStateVO
son = new DevStateVO(); son.setYaer(Time); son.setMth(i[j]);
son.setAdd_devcount(0); son.setAlarmcount(0); son.setFalseCount(0);
son.setModuleID(10); father.add(j, son); if (sonlist != null && sonlist.size()
> 0) { for (int k = 0; k < sonlist.size(); k++) { if (sonlist.get(k).getMth()
== i[j]) { father.set(j, sonlist.get(k)); } } } } return father;

如果比较的是是字符串类型就要换成sonlist.get(k).getMath().equals(i[j])。

热门工具换一换

SQL查询近31天、半年、一年、三年的数据 的sql 没有数据会在保留日期 后面加0 保证日期完整呈现