• 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

  • 相关阅读:
    idea 配置mapper.xml代码提示
    vue配置请求转发解决跨域问题
    MySQL 连接出现 Authentication plugin 'caching_sha2_password' cannot be loaded
    判断链表是否有环(Java实现)
    Java实现链表反转(借助栈实现)
    IHS代理遇到404的问题
    麒峰可视化表单设计器vue版本
    2021.5.30发布内容
    表单常见问题说明
    排序算法与查找算法在项目中的实际应用
  • 原文地址:https://www.cnblogs.com/hfliyi/p/2964057.html
Copyright © 2020-2023  润新知