SQL小记

select * from
(
        ---辅营类
             select 
             main.date as sale_date, 
             main.flightno as flight_no,
             main.oridate  as flight_date,
             main.typename  as product_type_name,
             main.typecode as product_type_code,
             main.name as product_name,
             main.code as product_code,            
             main.oricode as dpt_airpt,
             main.destcode as arrv_airpt,
             main.channelid as channel_name,
                      
             nvl(tempprice.price1,0)  as tickets_,
             nvl(tempprice.price2,0)  as cons_fare,
             nvl(tempprice.price3,0) as fuel_oil_fare,
             nvl(tempprice.price4,0)  as other_fare,
             nvl(tempprice.price5,0)  as attached_fare,
             nvl(tempprice.priceall,0)  as sales_amt
              from (                                                                   
select to_char(t5.lastedittime,'yyyy-MM-dd') date,  
t1.flightno    as flightno,
t1.oridate    as oridate,                                                                      
t4.submarketcode as code,                                                                                             
t4.submarketname as name, 
t6.typecode,t6.typename,                                                                                          
t1.oricode,t1.destcode, 
t1.channelid  as   channelid,
t2.price as price,
t2.flightid,
t2.mappingid                                                                         
from pss_order_flight t1  
join pss_order_pay t5 on t1.orderid=t5.orderid  and   t5.status = '2'    
           
left join pss_order_sub_market t4 on (t1.orderid=t4.orderid and t1.id=t4.flightid)
  left join pss_service_type t6 on t4.submarkettype = t6.typecode                                                       
  left join pss_order_cost t2 on t4.id=t2.mappingid                                         

)    main 
       left  join                      
  (
   select mappingid flightidp,
  sum(CASE WHEN costtype = 1 THEN 0 ELSE  0 END) AS  price1,
  sum(CASE WHEN costtype = 2 THEN 0 ELSE  0 END) AS  price2,  
  sum(CASE WHEN costtype = 3 THEN 0 ELSE  0 END) AS  price3,
  sum(CASE WHEN costtype = 4 THEN 0 ELSE  0 END) AS  price4,
  sum(CASE WHEN costtype = 5 THEN price ELSE  0 END) AS  price5,
  sum(price)  as  priceall
   from pss_order_cost   group by mappingid
union  all
  select detailid flightidp,
  sum(CASE WHEN detailtype = 1 THEN 0 ELSE  0 END) AS  price1,
  sum(CASE WHEN detailtype = 2 THEN 0 ELSE 0 END) AS  price2,  
  sum(CASE WHEN detailtype = 3 THEN 0 ELSE 0 END) AS  price3,
  sum(CASE WHEN detailtype = 4 THEN 0 ELSE 0 END) AS  price4,
  sum(CASE WHEN detailtype = 5 THEN -abs(refundfee) ELSE  0 END) AS  price5,
  sum(-abs(refundfee))  as  priceall
   from pss_order_ticket_refund_detail   group by detailid
  ) tempprice  on main.mappingid=tempprice.flightidp


         union all
          ---品牌类
            select 
             main.date as sale_date, 
             main.flightno as flight_no,
             main.oridate  as flight_date,
             main.typename  as product_type_name,
             main.typecode as product_type_code,
             main.name as product_name,
             main.code as product_code,            
             main.oricode as dpt_airpt,
             main.destcode as arrv_airpt,
             main.channelid as channel_name,
                      
             nvl(tempprice.price1,0)  as tickets_,
             nvl(tempprice.price2,0)  as cons_fare,
             nvl(tempprice.price3,0) as fuel_oil_fare,
             nvl(tempprice.price4,0)  as other_fare,
             nvl(tempprice.price5,0)  as attached_fare,
             nvl(tempprice.priceall,0)  as sales_amt
              from (                                                                   
select to_char(t5.lastedittime,'yyyy-MM-dd') date,  
t1.flightno    as flightno,
t1.oridate    as oridate,                                                               
t4.id as code,                                                                                                        
t4.name as name, 
'BRAND' as typecode,'品牌类' as typename,

t1.oricode,t1.destcode,  
t1.channelid  as   channelid,
t2.price  as price,
t2.flightid, 
t2.mappingid                                                                         
from pss_order_flight t1  
join pss_order_pay t5 on t1.orderid=t5.orderid  and   t5.status = '2'            
left join pss_farefamilies t4 on t4.id = t1.familycode                                                     
  left join pss_order_cost t2 on t1.id=t2.flightid  
  and  t1.oridate like '%2016-11-04%'
  and t1.flightno = '6206'
) 


  main 
       left  join                      
  (
   select flightid flightidp,
  sum(CASE WHEN costtype = 1 THEN price ELSE  0 END) AS  price1,
  sum(CASE WHEN costtype = 2 THEN price ELSE  0 END) AS  price2,  
  sum(CASE WHEN costtype = 3 THEN price ELSE  0 END) AS  price3,
  sum(CASE WHEN costtype = 4 THEN price ELSE  0 END) AS  price4,
  sum(CASE WHEN costtype = 5 THEN 0 ELSE  0 END) AS  price5,
  sum(price)  as  priceall
   from pss_order_cost   group by flightid
union  all
  select flightid flightidp,
  sum(CASE WHEN detailtype = 1 THEN -abs(refundfee) ELSE  0 END) AS  price1,
  sum(CASE WHEN detailtype = 2 THEN -abs(refundfee) ELSE 0 END) AS  price2,  
  sum(CASE WHEN detailtype = 3 THEN -abs(refundfee) ELSE 0 END) AS  price3,
  sum(CASE WHEN detailtype = 4 THEN -abs(refundfee) ELSE 0 END) AS  price4,
  sum(CASE WHEN detailtype = 5 THEN 0 ELSE  0 END) AS  price5,
  sum(-abs(refundfee))  as  priceall
   from pss_order_ticket_refund_detail   group by flightid
  ) tempprice  on main.flightid=tempprice.flightidp  


) tt
where tt.flight_date like '%2016-11-04%'
and tt.flight_no = '6206'
order by tt.sale_date