Oracle 分类显示各个分类前5条记录
原表:
-- Create table
create table LY_BG_TZGG
(
tzbh NVARCHAR2(60),
fsmc NVARCHAR2(60),
tzzt NVARCHAR2(60),
tznr NVARCHAR2(600),
yxsj NVARCHAR2(60),
fbsj NVARCHAR2(60),
zt NVARCHAR2(60) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
数据:
当前需求:按FSMC分类并按时间FBSJ倒序取到前5条记录
解决方案:(SQL)
select * from
(
select t1.fsmc,t1.tzzt,t1.fbsj,row_number() over(partition by fsmc order by t1.fbsj desc)rnum from ly_bg_tzgg t1
)
where rnum<6
结果: