简单_ SQL语句求教- 请进.该怎么处理

简单_ SQL语句求教--- 请进.......
帐表
 帐表No 修改No 内容
  1 0 aa
  1 1 bb
1 2 cc
1 3 dd
  2 0 xx
  2 1 yy
  3 0 ee
3 1 ff
3 2 gg
  4 0 mm
  
我要把修改次数大于2的帐表取出(Count(修改No)>2)
并且把最新和最新修改No-1)的数据取出.结果如下:
  1 cc dd
  3 ff gg

[解决办法]
试试

SQL code

select 帐表no      ,max(decode(rk,2,内容,'')) "最新修改"       ,max(decode(rk,1,内容,'')) "最新"from   (select 帐表no         ,修改no         ,内容          ,rank() over(partition by 帐表no order by 修改no desc) rk         ,count() over(partition by 帐表no) cnt    from 帐表)where cnt>2 and rk<=2group by 帐表no
[解决办法]
SQL code
 
 --DATA: 
 create table sa 
 ( 
    a01 int 
    ,a02 int 
    ,a03 nvarchar2(4) 
 )  
  
 insert into sa values(1,0,'aa'); 
 insert into sa values(1,1,'bb'); 
 insert into sa values(1,2,'cc'); 
 insert into sa values(1,3,'dd'); 
 insert into sa values(2,0,'xx'); 
 insert into sa values(2,1,'yy'); 
 insert into sa values(3,0,'ee'); 
 insert into sa values(3,1,'ff'); 
 insert into sa values(3,2,'gg'); 
 insert into sa values(4,0,'mm'); 
 commit; 
  
  
 --SQL: 
 select a01,max(case mm when 1 then a03  end) a 
     ,Max(case mm when 2 then a03  end) b 
 from  
 ( 
 select a01,a02,a03,row_number() over(partition by a01 order by a02) mm from  
   ( 
   select a01,a02,a03,row_number() over(partition by a01 order by a02 desc,a01) nn from sa 
   where a01 in 
   ( 
        select a01 from sa group by a01 having count(a01)>2 
   ) 
   ) 
 where nn <3  
 ) 
 group by a01 
  
 --RESULT: 
  
 1ccdd 
 3ffgg 
 

[解决办法]
http://topic.csdn.net/u/20081204/14/e2df503c-f7f1-42ef-b469-a40333d23cb5.html?1144766071