请教Oracle的一个高级查询
select Hl.lname, sum( to_number( Ht.org_txn_amt) - to_number( Ht.TXN_AMOUNT))/100 as HY ,count(1) as HC 
  from T_OfflineTradeDetails Ht 
  join t_card Hc on upper(Ht.drvcard_no)=hc.cw 
  join t_currentrelation Hcu on Hcu.cardno= Hc.cno  
  join t_line Hl on Hcu.lineid= Hl.lineid 
  where Ht.txn_flag in ('A4','87') and (Ht.card_type<>'06') 
  and ( Ht.TXN_DATE >='20100101' and Ht.TXN_DATE<='20130101') and ( Hl.lno in ('0988')) 
  group by Hl.lname ;

显示结果如下：
  LNAME HY HC
  988路 36846 36846  

 select Ll.lname, sum( to_number( Lt.org_txn_amt) - to_number( Lt.TXN_AMOUNT))/100 as LY ,count(1) as LC
  from T_OfflineTradeDetails Lt 
  join t_card Lc on upper(Lt.drvcard_no)=lc.cw 
  join t_currentrelation Lcu on Lcu.cardno= Lc.cno  
  join t_line Ll on Lcu.lineid= Ll.lineid where ( Lt.txn_flag in ('83','84') or ( Lt.txn_flag in ('A4','87') and (Lt.card_type='06') ))
  and ( Lt.TXN_DATE >='20100101' and Lt.TXN_DATE<='20130101') and ( Ll.lno in ('0988'))
  group by Ll.lname ;  
显示结果如下：
  LNAME LY LC
  988路 21588 10794

那怎么才能把两个查询合并在一起啊？显示的效果如下：
  LNAME HY HC LY LC
  988路 36846 36846 21588 10794
 

------解决方案--------------------

SQL code

with t1 as (
select Hl.lname, sum( to_number( Ht.org_txn_amt) - to_number( Ht.TXN_AMOUNT))/100 as HY ,count(1) as HC  
  from T_OfflineTradeDetails Ht  
  join t_card Hc on upper(Ht.drvcard_no)=hc.cw  
  join t_currentrelation Hcu on Hcu.cardno= Hc.cno   
  join t_line Hl on Hcu.lineid= Hl.lineid  
  where Ht.txn_flag in ('A4','87') and (Ht.card_type<>'06')  
  and ( Ht.TXN_DATE >='20100101' and Ht.TXN_DATE<='20130101') and ( Hl.lno in ('0988'))  
  group by Hl.lname
),
t2 as (
select Ll.lname, sum( to_number( Lt.org_txn_amt) - to_number( Lt.TXN_AMOUNT))/100 as LY ,count(1) as LC
  from T_OfflineTradeDetails Lt  
  join t_card Lc on upper(Lt.drvcard_no)=lc.cw  
  join t_currentrelation Lcu on Lcu.cardno= Lc.cno   
  join t_line Ll on Lcu.lineid= Ll.lineid where ( Lt.txn_flag in ('83','84') or ( Lt.txn_flag in ('A4','87') and (Lt.card_type='06') ))
  and ( Lt.TXN_DATE >='20100101' and Lt.TXN_DATE<='20130101') and ( Ll.lno in ('0988'))
  group by Ll.lname
)
select t1.lname, t1.hy, t1.hc, t2.ly, t2.lc from t1, t2 where t1.lname=t2.lname;