ORACLE 取唯一数据

set define off;
BEGIN
  -- 输出SQL文件名，便于从输出日志定位到SQL文件。
  dbms_output.put_line('01_CREATE_V_ITP_USER_ROLE.SQL');
  -- 输出SQL程序编写者，便于有问题快速找到编写者查问题
  dbms_output.put_line('作者: 刘*，IP:141');
END;
/ 
CREATE OR REPLACE VIEW V_ITP_USER_ROLE AS
SELECT TRA.EMPLOYEE_ID USER_ID,
       TRA.ROLE_ID ROLE_CODE,
       (SELECT DECODE(LENGTH(E.EMPLOYEE_FULL_NO),
                       10,2,1)  FROM TCM_EMPLOYEE E WHERE E.OID = TRA.EMPLOYEE_ID )  USER_TYPE,
       DECODE(TRA.ENNABLE_FLAG, 'T', '1', '0') STATUS,
       TO_CHAR(TRA.LASTUPDATEDTIME,'yyyy-mm-dd hh:mi:ss') LAST_UPDATED_DATE
  FROM (SELECT T.*,COUNT(1)OVER(PARTITION BY T.EMPLOYEE_ID,T.ROLE_ID,T.ENNABLE_FLAG ORDER BY T.ENNABLE_FLAG DESC, T.LASTUPDATEDTIME DESC)RN FROM TCM_ROLE_AUTH T)TRA WHERE TRA.RN=1; 
set define on;