select a_groupid from
(select a_groupid from a group by a order by agroupid desc)
where rownum<2
minus
select a_groupid from
(select a_groupid from a group by a order by agroupid desc)
where rownum<3
(select a_groupid from a group by a order by agroupid desc)
where rownum<2
minus
select a_groupid from
(select a_groupid from a group by a order by agroupid desc)
where rownum<3
(select a_groupid from a group by groupid order by a_groupid desc)
where rownum<2
minus
select a_groupid from
(select a_groupid from a group by groupid order by a_groupid desc)
where rownum<3
(select a.a_id as aid,a.a_groupid as agroupid from a,(select max(a_id) as a_id,a_groupid from a group by a_groupid) as b
where a.a_groupid=b.a_groupid and a.a_id!=b.a_id) as c
group by agroupid这样可以实现,不过我觉得不是捷径,求高人指点
可能我没说明白
我要的结果是
a_id a_groupid
2 1
3 2
2 3
.......
即把每一组 a_groupid相同的视为一个 a_groupid,但是对应的a_id 要对应这一组中第二大的。
a_groupid ,row_number()(paratition by a_id order by a_id desc) num
from tbl where num=2
(select
a_groupid ,row_number()(paratition by a_id order by a_id desc) num
from tbl ) t
where num=2
to: beckhambobo(beckham) 在rank()处提示“ora-30484:丢失的此函数窗口说明”
不会了!!