mysql查询一周内（每天）的记录

最新推荐文章于 2023-08-23 17:22:51 发布

原创最新推荐文章于 2023-08-23 17:22:51 发布 · 812 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #mysql

mysql 专栏收录该内容

8 篇文章

订阅专栏

该SQL语句用于查询指定用户在过去一周内每天的体温平均值。通过union all将每天的数据拼接，然后与设备设施数据表进行左连接，计算每一天的体温平均值。结果按日期升序排列，适用于健康监测场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

需求：查询一周内（每天）用户体温的平均值，结果要求如下：
sql语句

select * from (
	select a.cDate,IFNULL(b.cValue,0) as cValue from (
	SELECT DATE_FORMAT(date_sub(curdate(), interval 6 day),"%m.%d") as cDate,
	(select count(1) from device_facility_data where FROM_UNIXTIME(ctime/1000, "%m.%d") <![CDATA[ < ]]> 	DATE_FORMAT(date_sub(curdate(), interval 5 day),"%m.%d") and user_id = #{pageData.userId} and data_type=#{pageData.dataType}) as cValue
	union all
	SELECT DATE_FORMAT(date_sub(curdate(), interval 5 day),"%m.%d") as cDate,
	(select count(1) from device_facility_data where FROM_UNIXTIME(ctime/1000, "%m.%d") <![CDATA[ < ]]> DATE_FORMAT(date_sub(curdate(), interval 4 day),"%m.%d") and user_id = #{pageData.userId} and data_type=#{pageData.dataType}) as cValue
	union all
	SELECT DATE_FORMAT(date_sub(curdate(), interval 4 day),"%m.%d") as cDate,
	(select count(1) from device_facility_data where FROM_UNIXTIME(ctime/1000, "%m.%d") <![CDATA[ < ]]> DATE_FORMAT(date_sub(curdate(), interval 3 day),"%m.%d") and user_id = #{pageData.userId} and data_type=#{pageData.dataType}) as cValue
	union all
	SELECT DATE_FORMAT(date_sub(curdate(), interval 3 day),"%m.%d") as cDate,
	(select count(1) from device_facility_data where FROM_UNIXTIME(ctime/1000, "%m.%d") <![CDATA[ < ]]> DATE_FORMAT(date_sub(curdate(), interval 2 day),"%m.%d") and user_id = #{pageData.userId} and data_type=#{pageData.dataType}) as cValue
	union all
	SELECT DATE_FORMAT(date_sub(curdate(), interval 2 day),"%m.%d") as cDate,
	(select count(1) from device_facility_data where FROM_UNIXTIME(ctime/1000, "%m.%d") <![CDATA[ < ]]> DATE_FORMAT(date_sub(curdate(), interval 1 day),"%m.%d") and user_id = #{pageData.userId} and data_type=#{pageData.dataType}) as cValue
	union all
	SELECT DATE_FORMAT(date_sub(curdate(), interval 1 day),"%m.%d") as cDate,
	(select count(1) from device_facility_data where FROM_UNIXTIME(ctime/1000, "%m.%d") <![CDATA[ < ]]> DATE_FORMAT(date_sub(curdate(), interval 0 day),"%m.%d") and user_id = #{pageData.userId} and data_type=#{pageData.dataType}) as cValue
	union all
	SELECT DATE_FORMAT(curdate(),"%m.%d") as cDate,
	(select count(1) from device_facility_data where FROM_UNIXTIME(ctime/1000, "%m.%d") <![CDATA[ < ]]> DATE_FORMAT(date_sub(curdate(), interval -1 day),"%m.%d") and user_id = #{pageData.userId} and data_type=#{pageData.dataType}) as cValue
) as a left join (
	select FROM_UNIXTIME(ctime/1000, "%m.%d") cDate, 
	avg(data_value) as cValue
	from device_facility_data
	where user_id = #{pageData.userId}
	and data_type=#{pageData.dataType}
	group by FROM_UNIXTIME(ctime/1000, '%m.%d')
	) b on a.cDate = b.cDate
) as aa order by aa.cDate asc