应该说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
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
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
要是用sql都不行。那么程序就不要向了。单单从这语句没法看出问体。因为我们根本不知道你那张表的数据量多。。
要尽量避免多这样的表进行重复查询。或他们之间的连接。
select能不嵌套尽量不要嵌套。
这2个指导原则,会使你的sql快很多。找点sql优化的资料看看吧
但我觉得SELECT象狗皮膏药,百搭,而且是绝招,一般不轻易出手。。嘻嘻
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建成一个临时表,使用时先生成这个临时表,在和这个表做关联,就可以省去很多的遍例了
可以用存储过程来实现临时表的生成与删除。