将2个查询合并为1个查询，以便输出在单行上

Question

我正在尝试合并以下两个查询的输出，以便它们在一行中输出任何您可以提供的帮助，将非常感谢。

问题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

Answer 1

这还不够吗？

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);

Answer 2

或者只需像下面的伪代码那样修改您的联合：

    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