首頁(yè) 猿問(wèn) 現(xiàn)在用這個(gè)查詢要將近20分鐘，希望...

現(xiàn)在用這個(gè)查詢要將近20分鐘，希望能在5分鐘之內(nèi)查出來(lái)，數(shù)據(jù)量很大

SQL Server

當(dāng)年話下 2023-02-17 21:17:03

select gg.mer_id brhId, bb.brh_nm brhIdDesc, sum(casewhen gg.txn_flag in ('00') thengg.tran_amtelse0end) zhiFuTotalAmt, sum(casewhen gg.txn_flag in ('00') then1else0end) zhiFuTotalCount, sum(casewhen gg.txn_flag in ('02') thengg.tran_amtelse0end) tuiHuoTotalAmt, sum(casewhen gg.txn_flag in ('02') then1else0end) tuiHuoTotalCount from ((select a.pay_brh_id,b.mer_id, a.txn_amt as tran_amt,'02' as txn_flagfrom tbl_pgw_refund_dtl a,tbl_pgw_txn_dtl b,tbl_pgw_refund_info twhere a.order_id = b.order_idand a.refund_req = t.refund_reqand t.result in ('4', '7')and t.refund_type != '1'and t.refund_dt >= 20130401and t.refund_dt <= 20130423and b.mer_id !='888888888888888'and a.pay_brh_id = 888880188102900) union all(select d.bank_id as pay_brh_id, c.mer_id, d.tran_amt, '00' as txn_flagfrom TBL_PGW_TXN_DTL c, TBL_PGW_PAY_DTL dwhere c.order_id = d.order_idand c.rsp_code = '0000'and c.int_txn_dt >= 20130401and c.int_txn_dt <= 20130423and c.mer_id !='888888888888888'and d.bank_id = 888880188102900and d.trans_code in ('A0160', 'A0400')) union all(select a.pay_brh_id, f.send_brh_id as mer_id, a.txn_amt as tran_amt, '02' as txn_flag from tbl_pgw_refund_dtl a, tbl_pgw_txn_dtl b, tbl_pgw_refund_info t, tbl_csys_txn_log f where a.order_id = b.order_idand b.order_id = f.order_idand a.refund_req = t.refund_reqand t.result in ('4', '7')and t.refund_type != '1'and t.refund_dt >= 20130401and t.refund_dt <= 20130423and a.pay_brh_id = 888880188102900) union all(select d.bank_id as pay_brh_id,t.send_brh_id as mer_id,d.tran_amt,'00' as txn_flagfrom TBL_PGW_TXN_DTL c, TBL_PGW_PAY_DTL d,tbl_csys_txn_log twhere c.order_id = d.order_idand c.order_id = t.order_idand c.rsp_code = '0000'and c.int_txn_dt >= 20130401and c.int_txn_dt <= 20130423and d.bank_id = 888880188102900and d.trans_code in ('A0160', 'A0400'))) gg, tbl_saf_brh_info bb where gg.mer_id = bb.brh_id(+)group by gg.mer_id, bb.brh_nm

查看完整描述

2 回答

慕標(biāo)琳琳

TA貢獻(xiàn)1830條經(jīng)驗(yàn) 獲得超9個(gè)贊

tbl_pgw_refund_info:refund_req,result

tbl_pgw_refund_dtl :pay_brh_id
tbl_pgw_txn_dtl :order_id

TBL_PGW_TXN_DTL :int_txn_dt,rsp_code

TBL_PGW_PAY_DTL :order_id
在上述表的指定字段上建索引

反對(duì) 回復(fù) 2023-02-20

慕妹3242003

TA貢獻(xiàn)1824條經(jīng)驗(yàn) 獲得超6個(gè)贊

把語(yǔ)句中用到in的都改成exists

比如 select a,b from T where id in (1)  改成
select a,b from T t where exsits( select 1 from  t.id = 1)
你也可以查一下 SQL優(yōu)化中有一點(diǎn) 就是把int 轉(zhuǎn)化成 exsits

反對(duì) 回復(fù) 2023-02-20