四类垃圾分类重量统计

select
case when dry_garbage<0 then 0 else dry_garbage end dry_garbage,
case when wet_garbage<0 then 0 else wet_garbage end wet_garbage,
case when recyclable_garbage<0 then 0 else recyclable_garbage end recyclable_garbage,
case when hazardous_garbage<0 then 0 else hazardous_garbage end hazardous_garbage
from(
select
 dry_a-dry_b dry_garbage,
     wet_a-wet_b wet_garbage,
     recyclable_a-recyclable_b recyclable_garbage,
     hazardous_a-hazardous_b hazardous_garbage
     from
(
SELECT
        dry/1000 dry_a,
		wet/1000 wet_a,
		recyclable/1000 recyclable_a,
		hazardous hazardous_a
       from
(
SELECT IFNULL(SUM(net_weight), 0) dry
FROM data_weight
 where
        litter="生活"
           
            and DATE_FORMAT( collect_date, '%Y-%m-%d' ) = '2020-12-20'
            

       
)e,
(
SELECT IFNULL(SUM(net_weight), 0) wet
FROM data_weight
 where litter!="生活"
           
            and DATE_FORMAT( collect_date, '%Y-%m-%d' ) = '2020-12-20'
)f,
(
SELECT  IFNULL(SUM(recyclable_garbage), 0) recyclable FROM jq_garbage_compartment_data
where
 DATE_FORMAT( garbage_time, '%Y-%m-%d' ) = '2020-12-20'
           
            and garbage_id in (SELECT id FROM jq_garbage_compartment where equipment_type = 1 and is_del=0)

)g,
(
SELECT SUM(a.hazardous) hazardous
from(
SELECT IFNULL(SUM(hazardous_garbage)/25, 0) hazardous
FROM jq_community_data
where
DATE_FORMAT( save_time, '%Y-%m-%d' ) = '2020-12-20'
and is_del = '0'
        
UNION
SELECT IFNULL(SUM(hazardous_garbage)/1000, 0) hazardous
FROM jq_company_data
where
DATE_FORMAT( save_time, '%Y-%m-%d' ) = '2020-12-20'
and is_del = '0'
)a)h
)w,
(SELECT  IFNULL(SUM(dry_garbage)/25, 0) dry_b,
     IFNULL(SUM(wet_garbage)/25, 0) wet_b,
     IFNULL(SUM(recyclable_garbage)/25, 0) recyclable_b,
     IFNULL(SUM(hazardous_garbage)/25, 0) hazardous_b
      FROM jq_community_data
       where
DATE_FORMAT( save_time, '%Y-%m-%d' ) = '2020-12-20'
and is_del = '0'
)v
)y