结合2查询到1所以输出是在单行

问题描述：

我想结合以下两个查询的输出，所以他们在一行输出任何帮助，你可以提供将非常感激。结合2查询到1所以输出是在单行

查询1：

select FLT_NUM, SCH_DEP_DATE, sum (GROSS_WGT) as UWS_GROSS_WGT, sum (NETT_WGT) + SUM(BULK_WGT) as UWS_NETT_WGT 
from 
(select distinct 
     d.aln_code||d.flt_num as FLT_NUM, 
     trunc (d.sch_dep_date) flight_date, 
     d.brd_pnt, 
     d.off_pnt, 
     trunc(d.sch_dep_date) as Departure_date, 
     to_date(to_char(d.sch_dep_date, 'DD-MON-YYYY')||''||d.sch_dep_time_hrs||':'||d.sch_dep_time_min, 'DD-MON-YYYY HH24:MI') as SCH_DEP_DATE, 
     a.gross_wt as GROSS_WGT, 
     a.tare_wt as TARE_WGT, 
     a.nett_wt as NETT_WGT, 
     a.shpmnt_wt as BULK_WGT 


from cfr_leg d, cfr_uws_hdr e, CFR_LEG_SEG f,CFR_UWS_LOAD_DTLS a 
where d.fll_id = e.fll_id (+) 
    and d.fll_id = f.fll_id 
    and d.fll_id = a.fll_id 
    and d.flt_num = '0001' 
    and trunc (d.sch_dep_date) between '29-OCT-2016' and '29-OCT-2016' 
    and d.brd_pnt = 'LHR' 
    and f.opn_mode = 'J') 

group by FLT_NUM, SCH_DEP_DATE

查询2：

select 

FLT_NUM,SCH_DEP_DATE, sum (BKD_WGT), sum (BKD_VOL) 

from 
(Select distinct 
     a.aln_code||a.flt_num as FLT_NUM, 
     trunc(a.dep_date)as SCH_DEP_DATE, 
     b.doc_prfx, 
     b.doc_num, 
     a.bkd_wt as BKD_WGT, 
     a.bkd_vol as BKD_VOL  
from sbh_res_itnry a, sbh_car b, sbh_res c 
where c.car_id = b.car_id (+) 
    and c.car_id = a.car_id 
    and a.aln_code = 'VS' 
    and a.flt_num = '0001' 
    and trunc (a.dep_date) = '29-OCT-2016' 
    AND a.cncl_ind = 'N' 
    and b.cncl_ind = 'N' 
    and c.enq_ind = 'N') 

group by FLT_NUM,SCH_DEP_DATE

我一直在使用UNION ALL尝试，但它在2行

VS0001 29-Oct-2016    21365.09 92.404281 
VS0001 29-Oct-2016    22595  21907

我想看看输出数据输出如下：

FLT_NUM SCH_DEP_DATE UWS_GROSS_WGT UWS_NETT_WGT BKD WGT BKD_VOL 
VS0001 29-Oct-2016 21365.09  92.404281  22595 21907

你有在查询的列可能识别'UWS_GROSS_WGT'相到'UWS_NETT_WGT'？我正在寻找实际的字符串列来达到这个效果。如果是这样，那么你可以透视以获得你的输出。 –

是'a.gross_wt'是用于UWS_GROSS_WEIGHT –

答

这不够吗？

with q1 as (/*YOUR QUERY 1*/), q2 as (/*YOUR QUERY 2*/) 
select q1.FLT_NUM, q1.SCH_DEP_DATE, q1.UWS_GROSS_WGT, q1.UWS_NETT_WGT, q2."BKD WGT", q2.BKD_VOL 
from q1 inner join q2 on (q1.FLT_NUM = q2.FLT_NUM and q1.SCH_DEP_DATE = q2.SCH_DEP_DATE)

全面查询：

with q1 as (select FLT_NUM, SCH_DEP_DATE, sum (GROSS_WGT) as UWS_GROSS_WGT, sum (NETT_WGT) + SUM(BULK_WGT) as UWS_NETT_WGT 
from 
(select distinct 
     d.aln_code||d.flt_num as FLT_NUM, 
     trunc (d.sch_dep_date) flight_date, 
     d.brd_pnt, 
     d.off_pnt, 
     trunc(d.sch_dep_date) as Departure_date, 
     to_date(to_char(d.sch_dep_date, 'DD-MON-YYYY')||''||d.sch_dep_time_hrs||':'||d.sch_dep_time_min, 'DD-MON-YYYY HH24:MI') as SCH_DEP_DATE, 
     a.gross_wt as GROSS_WGT, 
     a.tare_wt as TARE_WGT, 
     a.nett_wt as NETT_WGT, 
     a.shpmnt_wt as BULK_WGT 


from cfr_leg d, cfr_uws_hdr e, CFR_LEG_SEG f,CFR_UWS_LOAD_DTLS a 
where d.fll_id = e.fll_id (+) 
    and d.fll_id = f.fll_id 
    and d.fll_id = a.fll_id 
    and d.flt_num = '0001' 
    and trunc (d.sch_dep_date) between '29-OCT-2016' and '29-OCT-2016' 
    and d.brd_pnt = 'LHR' 
    and f.opn_mode = 'J') 

group by FLT_NUM, SCH_DEP_DATE), 
q2 as (select 

FLT_NUM,SCH_DEP_DATE, sum (BKD_WGT) BKD_WGT, sum (BKD_VOL) BKD_VOL 

from 
(Select distinct 
     a.aln_code||a.flt_num as FLT_NUM, 
     trunc(a.dep_date)as SCH_DEP_DATE, 
     b.doc_prfx, 
     b.doc_num, 
     a.bkd_wt as BKD_WGT, 
     a.bkd_vol as BKD_VOL  
from sbh_res_itnry a, sbh_car b, sbh_res c 
where c.car_id = b.car_id (+) 
    and c.car_id = a.car_id 
    and a.aln_code = 'VS' 
    and a.flt_num = '0001' 
    and trunc (a.dep_date) = '29-OCT-2016' 
    AND a.cncl_ind = 'N' 
    and b.cncl_ind = 'N' 
    and c.enq_ind = 'N') 

group by FLT_NUM,SCH_DEP_DATE) 
select q1.FLT_NUM, q1.SCH_DEP_DATE, q1.UWS_GROSS_WGT, q1.UWS_NETT_WGT, q2.BKD_WGT, q2.BKD_VOL 
from q1 inner join q2 on (q1.FLT_NUM = q2.FLT_NUM and q1.SCH_DEP_DATE = q2.SCH_DEP_DATE);

对不起，我不确定如何设置我的查询这种方式你能给我一个例子，我可以如何构建我的查询使用我提供的2个查询。你提到的加入可以。 –

@jasonPalmer我发布了完整的查询。这是基于假设你的两个查询是正确工作 – Kacper

优秀作品完美，非常感谢你：o） –

答

或者只是修改你的工会像下面的伪代码：

SELECT FLT_NUM, SCH_DEP_DATE, sum(UWS_GROSS_WGT), sum(UWS_NETT_WGT), sum(BKD WGT), sum(BKD_VOL) 
    FROM( 
     select FLT_NUM, SCH_DEP_DATE, UWS_GROSS_WGT, UWS_NETT_WGT, 0 as BKD WGT , 0 as BKD_VOL from ... 
      union 
     select FLT_NUM, SCH_DEP_DATE, 0 as UWS_GROSS_WGT, 0 as UWS_NETT_WGT, BKD WGT , BKD_VOL from ... 
     ) 
    GROUP BY FLT_NUM, SCH_DEP_DATE

结合2查询到1所以输出是在单行

相关推荐