mysql统计30天内有效数据，数据为空返回0

最新推荐文章于 2023-06-29 15:00:51 发布

迷糊小葱

最新推荐文章于 2023-06-29 15:00:51 发布

阅读量665

点赞数

分类专栏：随手记

本文链接：https://blog.csdn.net/l_z_s_/article/details/111565994

版权

随手记专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MYSQL
返回30天内的统计数据(可用于echar折线图)

 SELECT 
       group_concat( t1.datetime ORDER BY t1.datetime ) AS date, 
       group_concat( COALESCE ( t2.STD_COVER, 0 ) ORDER BY t1.datetime ) AS stdConver
FROM ( 
	SELECT
		DATE_FORMAT( @cdate := date_add( @cdate, INTERVAL - 1 DAY ), '%Y%m%d' ) AS datetime, 0 AS countnum 
	FROM
		( SELECT @cdate := CURDATE() FROM TEMP_AA ) t1 WHERE @cdate > DATE_SUB( CURDATE(), INTERVAL 30 DAY ) ) t1
	LEFT JOIN ( SELECT DATE_FORMAT( cd.CREATE_DATE, '%Y%m%d' ) AS datetime, cd.COVER FROM TEMP_AA cd WHERE 1 = 1 GROUP BY datetime ) t2 ON t1.datetime = t2.datetime