luohe_back/各时间段统计订单缴费信息.txt

-- 按小时统计订单缴费信息
select 
time_id,
ifnull(amount,0) as amount
from 
luo_time_data b1
left join
(select
		 case when hour(c.pay_time) is null then 0 else hour(c.pay_time) end pay_time,
		 case when sum(amount) is null then 0 else round(sum(amount)/100,2) end as amount
FROM
	luo_park_baseinfo t
	left join 
(select a.park_id,a.pay_time,b.amount
from luo_parking_chargerecord a
inner join luo_parking_chargerecord_subject b
on a.order_no=b.order_no) c
ON t.park_id = c.park_id 
	where t.area_id = '411102' 
	AND t.keys_areas = '商圈' 
	AND t.street_name LIKE '%人民%' 
group by 1) c1
on b1.time_id=c1.pay_time


-- 按日统计订单缴费信息
select
		 case when day(c.pay_time) is null then 1 else day(c.pay_time) end pay_time,
		 case when sum(amount) is null then 0 else round(sum(amount)/100,2) end as amount
FROM
	luo_park_baseinfo t
	left join 
(select a.park_id,a.pay_time,b.amount
from luo_parking_chargerecord a
inner join luo_parking_chargerecord_subject b
on a.order_no=b.order_no) c
ON t.park_id = c.park_id 
where t.area_id = '411102' 
AND t.keys_areas = '商圈' 
AND t.street_name LIKE '%人民%' 
group by 1


-- 按周统计订单缴费信息
select
		 case when WEEKDAY(c.pay_time)+1 is null then 1 else WEEKDAY(c.pay_time)+1 end pay_time,
		 case when sum(amount) is null then 0 else round(sum(amount)/100,2) end as amount
FROM
	luo_park_baseinfo t
	left join 
(select a.park_id,a.pay_time,b.amount
from luo_parking_chargerecord a
inner join luo_parking_chargerecord_subject b
on a.order_no=b.order_no) c
ON t.park_id = c.park_id 
where t.area_id = '411102' 
AND t.keys_areas = '商圈' 
AND t.street_name LIKE '%人民%' 
group by 1
-- 按年统计订单缴费信息
select
		 case when YEAR(c.pay_time) is null then 2024 else YEAR(c.pay_time) end pay_time,
		 case when sum(amount) is null then 0 else round(sum(amount)/100,2) end as amount
FROM
	luo_park_baseinfo t
	left join 
(select a.park_id,a.pay_time,b.amount
from luo_parking_chargerecord a
inner join luo_parking_chargerecord_subject b
on a.order_no=b.order_no) c
ON t.park_id = c.park_id 
where t.area_id = '411102' 
AND t.keys_areas = '商圈' 
AND t.street_name LIKE '%人民%' 
group by 1