统计当天、当月、12个月信息的SQL

-- 12个月的数据 --
select ifnull(b.count, 0) count ,ifnull(c.count, 0) count2, a.month month from (
select 1 month union all
select 2 month union all
select 3 month union all
select 4 month union all
select 5 month union all
select 6 month union all
select 7 month union all
select 8 month union all
select 9 month union all
select 10 month union all
select 11 month union all
select 12 month
) a left join
(
select month(create_time) month, count(*) count from property_work_order
where
year(create_time)=year(now()) and del_flag = 0 and status != 4 and status != 8
group by date_format(create_time, '%Y-%m'), month
) b
on a.month=b.month left join
(
select month(create_time) month, count(*) count from property_work_order
where
year(create_time)=year(now()) and del_flag = 0 and status in (5,6)
group by date_format(create_time, '%Y-%m'), month
) c
on a.month=c.month order by month
-- 当天的数据 --
select ifnull(sum(a.fee_amount), 0) as shoukuantoday, 1 as id
from bill_list_detail a left join bill_list as b on a.pid = b.id
where a.del_flag = 0 and b.del_flag = 0 and b.pay_flag = 1 and b.confirmed = 1 and date_format(b.pay_date,'%Y-%m-%d')=date_format(curdate(),'%Y-%m-%d')
-- 当月的数据 --
select ifnull(sum(a.fee_amount), 0) as shoukuancurmonth, 1 as id
from bill_list_detail a left join bill_list as b on a.pid = b.id
where a.del_flag = 0 and b.del_flag = 0 and b.pay_flag = 1 and b.confirmed = 1 and date_format(b.pay_date,'%Y-%m')=date_format(curdate(),'%Y-%m')