ORA-01791:not a SELECTed expression 处理

今天工作中遇到一个问题：

执行sql “select distinct NU_TARGETID  from BMS_OWN.BMS_COMMENT where CH_TYPE = 1 order by DA_CREATEDATE desc”

语句的时候 出现ORA-01791:not a SELECTed expression错误

 

因为select distinct 和order by一起使用的时候，order by中必须是常量或者select列表中出现的表达式。

 

这样会出现逻辑错误。

 

对于单表来讲，如果NU_TARGETID  这个字段有unique index 并且字段有not null约束。那么这条语句是不会出错的。

如果是多个表join，这种情况我经过大量测试，发现仍然会出错，所以这种特殊性目前看来只存在于单表中。使用中大家注意。

至于为什么会出现这种特殊性，其他的要出错，而唯独满足以上红色部分条件不错，原因大概是这样的：

 假设NU_TARGETID  不唯一，返回时存在如下三条记录

  NU_TARGETID   DA_CREATEDATE

   A                       2009-11-12

   A                       2009-11- 14

   B                       2009-11-13

 执行distinct 后得到 A 、B （注意，这里没有管顺序），然后根据DA_CREATEDATE 字段来确定A、B记录的先后顺序，这个时候问题就来了，order by根据哪个DA_CREATEDATE 来排序来确定AB的位置呢？如果根据第一个，那么结果应该是AB，根据第二个结果应该是BA，确定不了了。。。。所以当然会报错。

如果NU_TARGETID   唯一，查询出来的记录即使不distinct也是唯一的，这一点数据库自己是明白的，那么order by时，当然可以排出序来。这样就不会出错。

所以不管是单表还是多表，只要oracle自己区分不出根据哪个来排序就会出现这个异常.

 

所以现在需要用max函数来解决这个问题,跳过适用ditinct 关键字。直接用max函数，来满足需求。

大意是这样的：用order by 是要去的同一个商家的评论的最新条数。既然这样就用max函数就行取到最新的记录，就没有必要用distinct了，这样就会避免distinct 和order by 冲突

具体的sql语句如下：

 

select NU_TARGETID, max(DA_CREATEDATE)
  from BMS_OWN.BMS_COMMENT
 where CH_TYPE = 1
 group by NU_TARGETID
 order by max(DA_CREATEDATE) desc