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

忘是亡心i 2021-10-09 10:12 424阅读 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)

![70][]

修改算法：可以查询到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

![70 1][]

最近一年的虚拟表

上面的时间虚拟表（将上面的时间表设置为：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\])。

[70]: /images/20211009/ae28ce2ca020472399e1b35a7173a1e5.png
[70 1]: /images/20211009/d56b7e425da846abb716e25fc3b2c3ab.png