oracle order by和 distinct并存报错“ORA-01791: 不是 SELECTed 表达式”

2019独角兽企业重金招聘Python工程师标准>>>

在项目脚本中会经常使用distinct来去除，当去重一不小心遇上order by是就会产生ora-01791错误

       select
         distinct
         a.accountno,
           a.instpdofferid,
           a.offer_id,
           a.standfeeid,
           a.cust_no,
           a.custaccid,
           a.subscriberid,
           a.feecode,
           a.accstatus,
           a.price,
           a.discount,
           a.money,
           a.returns,
           to_char(a.starttime,'yyyy-MM-dd') as starttime,
           to_char(a.endtime,'yyyy-MM-dd') as endtime,
           to_char(a.createtime,'yyyy-MM-dd') as createtime,
           a.cyccount,
           a.invoprint,
           a.billprint,
           a.logno,
           a.pay_type,
           a.paymentmoney,
           b.offer_name,
           c.standfeename,
           d.feename,
           a.accstatus,
           f_getparamname(a.accstatus,'ACCOUNT_ACCSTATUS') as accStatusName,
           a.quantily,
           a.remark,
           i.invo_no
      from t_account            a,
           t_pdoffer            b,
           t_prfeestandard      c,
           t_feetype            d, 
           t_acinvoiceprintdetail h,
           T_ACINVOICEPRINTLOG  i
     where a.offer_id = b.offer_id(+)
       and a.standfeeid = c.standfeeid(+)
       and a.feecode = d.feecode(+)
         and a.accountno=h.accountno(+) 
         and h.invoprintid = i.invoprintid(+)
         and a.cust_no = '00152851'
         order by a.createtime desc,a.accountno
         

正确是order by的字段一定要存在在select 查询字段，上面order by creatime没有在select 字段中，加上就好了

       select
         distinct
         a.accountno,
           a.instpdofferid,
           a.offer_id,
           a.standfeeid,
           a.cust_no,
           a.custaccid,
           a.subscriberid,
           a.feecode,
           a.accstatus,
           a.price,
           a.discount,
           a.money,
           a.returns,
           to_char(a.starttime,'yyyy-MM-dd') as starttime,
           to_char(a.endtime,'yyyy-MM-dd') as endtime,
           to_char(a.createtime,'yyyy-MM-dd') as createtime,
           a.cyccount,
           a.invoprint,
           a.billprint,
           a.logno,
           a.pay_type,
           a.paymentmoney,
           b.offer_name,
           c.standfeename,
           d.feename,
           a.accstatus,
           f_getparamname(a.accstatus,'ACCOUNT_ACCSTATUS') as accStatusName,
           a.quantily,
           a.remark,
           i.invo_no,
           a.createtime 
      from t_account            a,
           t_pdoffer            b,
           t_prfeestandard      c,
           t_feetype            d, 
           t_acinvoiceprintdetail h,
           T_ACINVOICEPRINTLOG  i
     where a.offer_id = b.offer_id(+)
       and a.standfeeid = c.standfeeid(+)
       and a.feecode = d.feecode(+)
         and a.accountno=h.accountno(+) 
         and h.invoprintid = i.invoprintid(+)
         and a.cust_no = '00152851'
         order by a.createtime desc,a.accountno
         

 

转载于:https://my.oschina.net/u/1380237/blog/777343