ORA-00904："RN"：无效标识符

Question

谁能给我解释一下为什么我会遇到这个错误，我错在哪里，以及将来要做些什么来避免它。

SELECT a.*, row_number() over (partition by code_employee order by  
to_date(substr(last_located_time,1,10),'yyyy-mm-dd')) rn
FROM
    (SELECT LAST_LOCATED_TIME, NAME_COMMON, CODE_EMPLOYEE
FROM 
     daily_report

 WHERE INSERT_DATE = TRUNC (SYSDATE-3) AND DEVICE_NAME LIKE '%SM-T116IR')a 
 WHERE rn=1;

Answer 1

请试试这个：

select *
  from (select a.*,
               row_number() over(order by to_date(last_located_time, 'dd.mm.rrrr')) rn
          from (select last_located_time, name_common, code_employee
                  from daily_report                
                 where insert_date = trunc(sysdate-3) and device_name like '%SM-T116IR') a)
 where rn = 1