• oracle row_number()


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

  • 相关阅读:
    Spring Boot相关组件的添加
    递归详解
    SpringBoot的特性
    常规属性配置
    SpringBoot集成mybatis和mybatis generator
    Profile配置
    SpringBoot的运行原理
    Thymeleaf模板引擎
    入口类和@SpringBootApplication
    C++的rand()
  • 原文地址:https://www.cnblogs.com/hfliyi/p/2964057.html
Copyright © 2020-2023  润新知