我可以将这两个查询组合成一个查询吗?

roqulrg3  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(382)
Note:AUT_GROUP_ID column in Table CBS_OWNER.AUT_GROUP_MAP is the only factor differentiating both queries.  
AUT_GROUP_ID IN (20,4,7) --->ONNET
AUT_GROUP_ID IN (3,6) ---> OFFNET

第一个查询正在获取服务“onnet”的所有用法详细信息

select A.AUT_ID,
A.Service,
A.CDR_DATE,
(case when A.UNIT_TYPE_CB=1 then A.UNITS_CB/10000 when A.UNIT_TYPE_CB=2 then A.UNITS_CB/60 end )as USAGE,
(case when A.UNIT_TYPE_CB=1 then 'Currency' when A.UNIT_TYPE_CB=2 then 'Seconds' end )as UNIT_TYPE,
A.UNIT_TYPE_CB,
B.PAYMENT_MODE,
(case when B.PAYMENT_MODE=1 then 'Prepaid' else 'postpaid' end ) as PAYMENT_TYPE
from 
(select 'ONNET' as Service,
cd.AUT_ID as AUT_ID,
cd.expected_cutoff_dt as CDR_DATE,
cd.PAYMENT_MODE as POST_PRE_TYPE,
cb.UNITS as UNITS_CB,
cb.UNIT_TYPE as UNIT_TYPE_CB

FROM cbs_owner.cdr_balance cb left  join  cbs_owner.cdr_data cd
on  cb.MSG_ID = cd.MSG_ID
and   cb.MSG_ID2 = cd.MSG_ID2 where  cb.EXPECTED_CUTOFF_DT='09-MAY-20'  and cd.EXPECTED_CUTOFF_DT='09-MAY-20' and  cd.AUT_ID in 
    (SELECT DISTINCT AUT_ID
      FROM CBS_OWNER.AUT_GROUP_MAP
     WHERE AUT_GROUP_ID IN (20,4,7)
       AND RESELLER_VERSION_ID IN (SELECT MAX(RESELLER_VERSION_ID)
                                     FROM CBS_OWNER.RESELLER_VERSION
                                    WHERE STATUS = 3)))A ,CBS_OWNER.offer_ref B where A.OFFER_ID_CD=b.offer_id;

第二个查询正在获取服务“offnet”的所有用法详细信息和余额详细信息

select A.AUT_ID,
A.Service,
A.CDR_DATE,
(case when A.UNIT_TYPE_CB=1 then A.UNITS_CB/10000 when A.UNIT_TYPE_CB=2 then A.UNITS_CB/60 end )as USAGE,
(case when A.UNIT_TYPE_CB=1 then 'Currency' when A.UNIT_TYPE_CB=2 then 'Seconds' end )as UNIT_TYPE,
A.UNIT_TYPE_CB,
B.PAYMENT_MODE,
(case when B.PAYMENT_MODE=1 then 'Prepaid' else 'postpaid' end ) as PAYMENT_TYPE
from 
(select 'OFFNET' as Service,
cd.AUT_ID as AUT_ID,
cd.expected_cutoff_dt as CDR_DATE,
cd.PAYMENT_MODE as POST_PRE_TYPE,
cb.UNITS as UNITS_CB,
cb.UNIT_TYPE as UNIT_TYPE_CB
    FROM cbs_owner.cdr_balance cb left  join  cbs_owner.cdr_data cd
on  cb.MSG_ID = cd.MSG_ID
and   cb.MSG_ID2 = cd.MSG_ID2 where  cb.EXPECTED_CUTOFF_DT='09-MAY-20'  and cd.EXPECTED_CUTOFF_DT='09-MAY-20' and  cd.AUT_ID in 
    (SELECT DISTINCT AUT_ID
      FROM CBS_OWNER.AUT_GROUP_MAP
     WHERE AUT_GROUP_ID IN (3,6)
       AND RESELLER_VERSION_ID IN (SELECT MAX(RESELLER_VERSION_ID)
                                     FROM CBS_OWNER.RESELLER_VERSION
                                    WHERE STATUS = 3)))A ,CBS_OWNER.offer_ref B where A.OFFER_ID_CD=b.offer_id;

如何将这两个查询合并为一个查询?

oxiaedzo

oxiaedzo1#

SELECT 
  <all those columns that irrelevant to the question>
FROM  
 (
  (select 'OFFNET' as Service,
    <lots of stuff>
    and rownum <300)
  ) UNION ALL
  (select 'ONNET' as Service,
    <lots of stuff>
    and rownum <300)
 )

你也许可以简化,但这是可行的。下次发布问题时,请尝试从语句中抽象出问题,甚至可以在示例数据集上重现。它会让你更好地理解这个问题,甚至可能在你发布它之前解决它,人们会更容易帮助你。

相关问题