select
a.cust_name,
b.type ,
size20' = sum(decode(a.container_size,'20'',1,0)),
size40' = sum(decode(a.container_size,'40'',1,0))
from
order_info a,
(select '拖装' as type,* trucking_plan
union all
select '场装' as type,* fielding_plan) b
cont_over c
where
a.container_id = b.container_id
and
b.truck_field_id = c.truck_field_id
and
c.finished_date between to_date('2005-01-01','YYYY-MM-DD') and to_date('2005-04-30','YYYY-MM-DD')
group by
a.cust_name,b.type
order by
a.cust_name,b.type
a.cust_name,
b.type ,
size20' = sum(decode(a.container_size,'20'',1,0)),
size40' = sum(decode(a.container_size,'40'',1,0))
from
order_info a,
(select '拖装' as type,* trucking_plan
union all
select '场装' as type,* fielding_plan) b
cont_over c
where
a.container_id = b.container_id
and
b.truck_field_id = c.truck_field_id
and
c.finished_date between to_date('2005-01-01','YYYY-MM-DD') and to_date('2005-04-30','YYYY-MM-DD')
group by
a.cust_name,b.type
order by
a.cust_name,b.type
(select '拖装' type ,a1.trucking_id truck_field_id,a1.contrainer_id from trucking_plan a1
union all
select '场装' type ,a2.fielding_id truck_field_id,a2.contrainer_id from fielding_plan a2) a,cont_over b,order_info c
where b.finished_date between to_date('2005-01-01','YYYYMMDD') and to_date('2005-04-30','YYYYMMDD')
and a.truck_field_id = b.truck_field_id
and a.contrainer_id = c.contrainer_id
group by c.cust_name,a.type