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;