请高手优化以下SQL!!!

Question

觉得以下SQL有点繁琐，只是对一张表操作，想不出好的方法。请高手优化！谢谢诶
select *
  from p_bzrj_yw_t
 where (rydh, bbh) in (select rydh, bbh
                         from p_bzrj_yw_t
                        where (rydh, bbh) in (select rydh, max(bbh)
                                                from p_bzrj_yw_t t
                                               where lcbs = '15'
                                                 and xzzzqx < '20090810'
                                               group by rydh)
                        group by rydh, bbh
                       having count(*) < 2)
如下表：
RYDH              BBH      LCBS      CZDW     YWBH
C052008B0121 00 15 010600 01060008006761
C052008B0121 01 15 010600 01060008006762
C052008B0121 02 15 010600 01060008006763
C052008B0124 00 15 010600 01060008006764
C052008B0124 01 15 010600 01060008006765
C052008B0124 01 15 010600 01060008006766
C052008B0129 00 15 010600 01060008006769
C052008B0132 00 15 010600 01060008006772
C052008B0135 00 15 010600 01060008006775目标选出：相同RYDH中最大BBH只有一条的那些记录。
C052008B0121 02  15   010600 01060008006763
C052008B0129 00  15   010600 01060008006769
C052008B0132 00  15   010600 01060008006772
C052008B0135 00  15   010600 01060008006775

Answer 1

把一个表当成两个表用可能会好点，另外用in的效率没有exists高

Answer 2

select rydh,bbh,lcbs,czdw,ywbh from(
   select rydh,bbh,lcbs,czdw,ywbh,rn,
     lag(bbh)over(partition by rydh order by rn desc)lg from(
      select rydh,bbh,lcbs,czdw,ywbh,
        row_number()over(partition by rydh order by bbh desc)rn from test_b))
   where rn=1 and bbh<>nvl(lg,'*') 
不是很难，只是楼主诚意不够哦