帐表
帐表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
帐表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
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<=2
group by 帐表no
--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:1 cc dd
3 ff gg