但行好事
莫论前程❤

Oracle 学习笔记—group by

group by 注意事项

`group by`的参数,要把查询字段中的非聚合函数都加到`group by`后面
`group by`后面的参数不能使用别名.

如果不遵循上诉说明,则会报错
Oracle学习笔记—mergo into的使用方法

  INSERT  into eprk_accinto_lx_b NOLOGGING (  
      SELECT '0001'||'EP'||to_char(SEQ_OID.Nextval, 'FM00000000000000') pk_accinto_lx_b,t.* from  
           (select '" + accinto_lxVO.getPk_accinto_lx() + "' pk_accinto_lx,  
                ehi.pk_areamanage pk_areamanage,  
                ehi.pk_corporation,  
                ehi.pk_person,  
                    '" + getDate() + "' accinto_date,  
                    '" + start_date + "' START_DATE,  
                    '" + end_date + "' END_DATE,  
      --//lp 20191124 待处理
             SUM(NVL(ehi.PSN_INTRST, 0)+NVL(ehi.CORP_INTRST, 0)+NVL(ehi.CORP_V_INTRST, 0)) ACCINTO_MONEY,  
                    '0' ARRIVED_STATUS,  
                    '0' TOGETHERSTATUS,  
                    '0' billstatus,  
                    '0001AA10000000009H1V' PK_OPERATOR,  
                    '" + getDate() + "' OPERATOR_DATE,  
                    '" + getDateTime() + "' OPERATOR_TIME,  
                    '' PK_DISCARDER,  
                    '' DISCARDER_DATE,  
                    '' DISCARDER_TIME,  
                    '" + getDateTime() + "',0,  
                    " +  Accinto_type.ACC_JX.getShort() + "  accinto_type  
           from eprk_hst_intrst ehi  
           where ehi.pk_corporation in (" + pk_corporation + ")  
             and ehi.intrst_year_month >= '" + start_date + "'  
           and ehi.intrst_year_month <= '" + end_date + "'  
             and ehi.if_accinto = 0  
        group by ehi.pk_areamanage, ehi.pk_person,ehi.pk_corporation)t)

参数值为

NVL(ehi.PSN_INTRST, 0)+NVL(ehi.CORP_INTRST, 0)+NVL(ehi.CORP_V_INTRST, 0) ACCINTO_MONEY

group by可以写成

group by ehi.pk_areamanage, ehi.pk_person,ehi.pk_corporation,(NVL(ehi.PSN_INTRST, 0)+NVL(ehi.CORP_INTRST, 0)+NVL(ehi.CORP_V_INTRST, 0))

group by不可以写成

group by ehi.pk_areamanage, ehi.pk_person,ehi.pk_corporation, ACCINTO_MONEY
赞(1) 打赏
未经允许不得转载:刘鹏博客 » Oracle 学习笔记—group by
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏