(select e.p_sid,b.p_state,c.p_psnum,c.p_panum,c.p_mbankid
from p_pss b,p_mccinfo c,p_sid e
where b.p_id=e.p_id
and b.p_id=c.p_id
and c.p_mbankid=b.p_mbankid
and b.p_state in ('01','03','04','05')
and (c.p_psnum is not null and c.p_pastate='00')) f
将表f建成一个临时表,使用时先生成这个临时表,在和这个表做关联,就可以省去很多的遍例了
可以用存储过程来实现临时表的生成与删除。
select a.p_id,a.p_sid,a.p_cardid,a.p_bankid,f.p_state,f.p_psnum,f.p_panum
from (select p_id,p_sid,p_cardid,p_bankid from p_detail
where (p_state in ('01','04') and p_cardid is not null
and p_cardid not in (
select p_cardid from p_card where p_rec=1)
)) a,
p_card d,
(
select e.p_sid,b.p_state,c.p_psnum,c.p_panum,c.p_mbankid
from p_pss b,p_mccinfo c,p_sid e
where b.p_id=e.p_id and b.p_id=c.p_id and c.p_mbankid=b.p_mbankid
and b.p_state in ('01','03','04','05') and (c.p_psnum is not null and c.p_pastate='00')) f
where a.p_caardid=d.p_cardid and a.p_sid=f.p_sid and f.p_mbankid=d.p_bankid and d.p_rec=0 order by a.p_sid
应该说5张表才对,大家请看看。
select a.p_id,a.p_sid,a.p_cardid,a.p_bankid,f.p_state,f.p_psnum,f.p_panum from p_detail a,p_card d, (select e.p_sid,b.p_state,c.p_psnum,c.p_panum,c.p_mbankid from p_pss b,p_mccinfo c,p_sid e where b.p_id=e.p_id and b.p_id=c.p_id and c.p_mbankid=b.p_mbankid and b.p_state in ('01','03','04','05')and (c.p_psnum is not null and c.p_pastate='00')) f where a.p_caardid=d.p_cardid and a.p_sid=f.p_sid and f.p_mbankid=d.p_bankid and (a.p_state in ('01','04') and a.p_cardid is not null and a.p_cardid not in (select p_cardid from p_card where p_rec=1)) and d.p_rec=0 order by a.p_sid