sql问题

Question

 SELECT a.pid,a.pname,a.fatherid,a.psynopsis,a.ptopic,a.ptaxis,a.pstick,a.plogo,(select count(kid) from bbs_stick where to_char(sysdate,'yyyy-MM-dd') = to_char(ktime,'yyyy-MM-dd') and pid = a.pid) num, kid,ktitle from (select pid,pname,fatherid,psynopsis,ptopic,pstick,ptaxis,plogo from bbs_space  where fid="+fid+" and ptype=2 and pstate=1 ORDER BY ptaxis ) a, (SELECT bs1.kid,bs1.pid,bs2.ktitle FROM (select max(kid) kid,pid from bbs_stick where kmainid=0 and kpit=1 and kshield=1 GROUP BY pid) bs1,bbs_stick bs2 WHERE bs1.kid=bs2.kid) b WHERE a.pid = b.pid(+)   红色的查询是查列，所以查询出来的速度很慢，现在不想这样查询，我试过用左连接，右连接和内连接，但找不到and pid = a.pid这个关系，请个位大虾帮帮忙...

Answer 1

SELECT A.PID,
       A.PNAME,
       A.FATHERID,
       A.PSYNOPSIS,
       A.PTOPIC,
       A.PTAXIS,
       A.PSTICK,
       A.PLOGO,
       KID_CT,
       KID,
       KTITLE
  FROM (SELECT PID,
               PNAME,
               FATHERID,
               PSYNOPSIS,
               PTOPIC,
               PSTICK,
               PTAXIS,
               PLOGO
          FROM BBS_SPACE
         WHERE FID = "+fid+"
           AND PTYPE = 2
           AND PSTATE = 1
         ORDER BY PTAXIS ----ORDER BY 可以去掉
 ) A,
       (SELECT BS1.KID,
               BS1.PID,
 KID_CT,
               BS2.KTITLE
          FROM (SELECT MAX(KID) KID,
                       PID,
 SUM(DECODE(TRUNC(KTIME),TRUNC(SYSDATE),1,0)) AS KID_CT
                  FROM BBS_STICK
                 WHERE KMAINID = 0
                   AND KPIT = 1
                   AND KSHIELD = 1
                 GROUP BY PID) BS1,
               BBS_STICK BS2
         WHERE BS1.KID = BS2.KID) B
 WHERE A.PID = B.PID(+)