自己构建临时表获取连续日期--最终版

select nowMonth, ifnull(max(count_num),0) as num
from (

	SELECT DATE_FORMAT(date_add('2022-02-01', INTERVAL xc DAY), '%Y-%m-%d') as nowMonth
	FROM ( 
	 SELECT @xi:=@xi+1 as xc from 
	 (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1, 
	 (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2,  
	 (SELECT @xi:=-1) xc0 
	) xcxc

) as dtable
left join t_test_date td on td.create_time = dtable.nowMonth
where DATE_FORMAT(nowMonth, ‘%Y-%m’) = DATE_FORMAT(now(), ‘%Y-%m’)
GROUP BY dtable.nowMonth
ORDER BY dtable.nowMonth asc

SELECT DATE_FORMAT(DATE_SUB(‘2022-01-09’, INTERVAL d DAY), ‘%Y-%m-%d’) as year_month_day
FROM (
select d from (
SELECT @xi:=@xi+1 as d from
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
(SELECT @xi:=-1) xc0
) as m where d <= 5
) dtc

瞎写点东西
瞎写点东西
select
coalesce((case when CONCAT(vsc.run_date,’’) = ‘2021-07-01’ then max(vsc.electric_control_max) END),0) as ‘2021-07-01’ ,
(case when CONCAT(vsc.run_date,’’) = ‘2021-07-02’ then max(vsc.electric_control_max) END) as ‘2021-07-02’ ,
(case when CONCAT(vsc.run_date,’’) = ‘2021-07-03’ then max(vsc.electric_control_max) END) as ‘2021-07-03’ ,
(case when CONCAT(vsc.run_date,’’) = ‘2021-07-04’ then max(vsc.electric_control_max) END) as ‘2021-07-04’ ,
coalesce((case when CONCAT(vsc.run_date,’’) = ‘2021-07-21’ then max(vsc.electric_control_max) END),0) as ‘2021-07-21’

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值