要求查询每个用户对应的最大样品信息，忽然想到ms sql提供过 row_number() over(partition by 列 order by 列 desc），那么oracle可能也存在，

我的表结构如下：

create table NEOGOODSRULE
(
  ID          NUMBER(22) not null,
  PERSONALID  NVARCHAR2(50),
  CT_SMP_TYPE NVARCHAR2(100)
)
tablespace VGSM
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

数据如下：

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2270, \'JYZ\', \'原料\');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2271, \'JYZ\', \'辅料\');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2359, \'SYSTEM\', \'包材(内)\');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2360, \'SYSTEM\', \'包材(外)\');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2361, \'SYSTEM\', \'原料\');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2362, \'SYSTEM\', \'成品\');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2363, \'SYSTEM\', \'稳定性(加速)\');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2364, \'SYSTEM\', \'稳定性(长期)\');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2365, \'SYSTEM\', \'辅料\');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2354, \'LY\', \'成品\');

insert into neogoodsrule (ID, PERSONALID, CT_SMP_TYPE)
values (2355, \'LY\', \'原料\');

ROW_NUMBER（）语法如下：

1、row_number() over(order by column asc)先对列column按照升序，再为每条记录返回一个序列号：

select personalid,row_number() over(order by personalid asc) rn from  neogoodsrule

2、row_number() over(partition by column1 order by column2 asc) 先按照column1分组，再对分组后的数据进行以column2升序排列

select personalid,ct_smp_type,row_number() over(partition by personalid order by ct_smp_type asc) rn from neogoodsrule 

 

由此，开始所提的需求sql代码如下

select * from (select personalid,ct_smp_type,row_number() over(partition by personalid order by ct_smp_type asc) rn from neogoodsrule )
where rn=1

版权声明：本文为hfliyi原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/hfliyi/archive/2013/03/17/2964057.html