看看这个题目要用哪些函数,有没有可能在一个SQL语句中完成?

Question

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

Answer 1

select c.cust_name,a.type,sum(decode(a.container_size,'20',1,0)) size20,sum(decode(a.container_size,'40',1,0)) size40 from
(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