MySQL 统计某年某月中所有天数的数据没有自动补0

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

林云记忆

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

阅读量301

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_47721612/article/details/128291087

版权

select aa.DAY time,
IFNULL( bb.inMax,“-”) inMax ,
IFNULL( bb.inMin,“-”) inMin ,
IFNULL( bb.outMax,“-”) outMax ,
IFNULL( bb.outMin,“-”) outMin ,
IFNULL( bb.deviceId,“-”) deviceId
from (
SELECT
date_add(DATE_ADD(“2022-12-01”, INTERVAL - DAY(“2022-12-01”) + 2 DAY),
INTERVAL (cast( help_topic_id AS signed INTEGER ) - 1 ) DAY
) DAY
FROM
mysql.help_topic
WHERE
help_topic_id < DAY ( last_day( “2022-12-01” ) )
ORDER BY
help_topic_id) aa
left join
(
select
DATE_FORMAT(created_at,“%Y-%m-%d”) time,
max(inlet_pressure) inMax,
min(inlet_pressure) inMin,
max(outlet_pressure) outMax,
min(outlet_pressure) outMin,
device_id deviceId
from pressure_statistics where deleted_at=0
and date_format(created_at, “%Y-%m”)=“2022-12”) bb on bb.time =aa.DAY
order by aa.DAY