oracle使用rownum函数，返回重复数据

 原始代码 - - 返回重复的数据

select *
from (select A.USER_CODE, A.ACCOUNT, B.SECU_ACC, B.MARKET
      from ACCOUNTS A
               join SECU_ACC B on B.CUST_CODE = A.USER_CODE
               cross join STDS_CUST_INFO C
      where B.MARKET = 0
        and C.OPEN_SIGN = 'O'
      order by A.USER_CODE)
where rownum <= 10;

 最终代码 - - 返回不重复的数据。将三表联查修改为两表联查后再与第三张表查询，join怀疑数据量太大一直加载中，故使用left join

select *
from (select *
      from (select A.USER_CODE, A.ACCOUNT, B.SECU_ACC, B.MARKET
            from ACCOUNTS A
                     left join SECU_ACC B on B.CUST_CODE = A.USER_CODE
            where B.MARKET = 0
            group by A.USER_CODE, A.ACCOUNT, B.SECU_ACC, B.MARKET
            order by A.USER_CODE)
      where rownum <= 100) F
         left join STDS_CUST_INFO C ON F.USER_CODE = C.CUST_CODE;

 根据网络找到的代码，返回了ROWNUM_编号

select *
from (
    select rownum rownum_
from (select A.USER_CODE, A.ACCOUNT, B.SECU_ACC, B.MARKET
      from ACCOUNTS A
               join SECU_ACC B on B.CUST_CODE = A.USER_CODE
               cross join STDS_CUST_INFO C
      where B.MARKET = 0
        and C.OPEN_SIGN = 'O'
      order by A.USER_CODE)
where rownum <= 100) D
where D.rownum_ > 10;

 此段代码可用，但处理过慢

select *
from (select A.USER_CODE, A.ACCOUNT, B.SECU_ACC, B.MARKET
      from ACCOUNTS A
               left join SECU_ACC B on B.CUST_CODE = A.USER_CODE
               cross join STDS_CUST_INFO C
      where B.MARKET = 0
        and C.OPEN_SIGN = 'O'
      group by A.USER_CODE, A.ACCOUNT, B.SECU_ACC, B.MARKET
      order by A.USER_CODE,SECU_ACC ASC )
where rownum <= 10;

根据其up主所说，rownum出现重复数据原因有，排序字段有重复或者排序字段存在null时，会导致没法排序，row_id就会无法确定，此时，可以加多一个排序条件