2 rp1qfd rp1qfd 于 2015.07.16 14:43 提问

ORACLE怎么写先排序后分组的前5位

select *
from (select a.*,
row_number() over(partition by ordered order by cdate desc) px
from (select a.*, b.user_name username
from view_hjzx a, gjjx.sys_users b
where a.userid = b.userid(+)
and a.HANDSET like '%15901054535%') a)
where px <= 5 order by cdate desc
数据是好几种分类,标识是ORDERED,然后我想达到的目的是,最大的日期的ORDERED作为一组,显示前五条,然后按日期排序,第二大的ORDERED前五条,第三大的前五条,日期带领5条组号,不知道怎么写,目前的写法有个问题就是后排序,假设第二组有个日期大于第一组他会跑上去,很苦恼
604790441欢迎指导

2个回答

Tiger_Zhao
Tiger_Zhao   Rxr 2015.07.16 15:09
已采纳
    select *
    from (select a.*,
    row_number() over(partition by ordered order by cdate desc) px,
    FIRST_VALUE(cdate) OVER (partition by ordered order by cdate desc
                             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                            ) ordered_date
    from (select a.*, b.user_name username
    from view_hjzx a, gjjx.sys_users b
    where a.userid = b.userid(+)
    and a.HANDSET like '%15901054535%') a)
    where px <= 5
    order by ordered_date DESC, ORDERED DESC, cdate desc
Tiger_Zhao
Tiger_Zhao   Rxr 2015.07.16 14:56
    where px <= 5 order by ORDERED DESC, cdate desc
rp1qfd
rp1qfd 以日期为导向的,先日期,后组,再5条
2 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片