要求查询每个用户对应的最大样品信息,忽然想到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