중복 데이터 제거 문제입니다. 0 2 205

by 시리 [SQL Query] mssql [2018.08.14 14:16:28]


안녕하세요.

중복 데이터 처리 건에 대해 질문 드립니다!

MSSQL 쿼리입니다.

 

select
            user0_.USER_ID as col_0_0_,
            user0_.USER_NM as col_1_0_,
            dept1_.DEPT_ID as col_2_0_,
            dept1_.DEPT_NM as col_3_0_,
            agentuserl2_.HOST_NM as col_4_0_,
            agentuserl2_.MULTI_IP as col_5_0_,
            agentuserl2_.LAST_LOGIN_TIME as col_6_0_,
            agentuserl2_.LAST_LOGOUT_TIME as col_7_0_,
            agentuserl2_.LOG_DT as col_8_0_,
            agentuserl2_.VERSION as col_9_0_,
            agentuserl2_.AGENT_LOG_TYPE_CD as col_10_0_
        from
            CM_USER user0_
        inner join
            CM_DEPT dept1_
                on user0_.DEPT_ID=dept1_.DEPT_ID
        left outer join
            DS_LOG_AGENT agentuserl2_
          on user0_.USER_ID=agentuserl2_.USER_ID
  order by
            agentuserl2_.LOG_DT desc;

 

 

결과값이

 

col_0_0_ col_1_0_ col_2_0_ col_3_0_ col_4_0_ col_5_0_ col_6_0_ col_7_0_ col_8_0_ col_9_0_ col_10_0_
user01 사용자01 dept01 인재개발센터 QC_VM-PC 192.168.3.25 2017-10-11 13:28:18.113 NULL 2017-10-11 13:28:18.113 0 I
user01 사용자01 dept01 인재개발센터 QC_06 192.168.4.206 2017-10-11 13:21:41.760 NULL 2017-10-11 13:21:41.760 0 I
user03 사용자03 dept02 전자문서사업부 DESKTOP-ANCF69C 192.168.4.19 2017-08-18 13:01:13.997 NULL 2017-08-16 11:09:27.160 0 I
user01 사용자01 dept01 인재개발센터 QC_08 192.168.4.208 2017-08-08 16:05:45.170 NULL 2017-08-07 17:40:33.703 0 I
user01 사용자01 dept01 인재개발센터 NULL 127.0.0.1 2017-10-23 17:53:42.340 NULL 2017-07-21 13:38:27.150 12345 I
user01 사용자01 dept01 인재개발센터 Terster-PC 10.0.2.15 2017-07-07 14:55:20.047 NULL 2017-07-07 14:55:20.047 0 I
user01 사용자01 dept01 인재개발센터 Terster-PC 10.0.2.15 2017-07-07 14:35:08.253 NULL 2017-07-07 14:35:08.253 0 I

 

이런 식으로 같은 아이디의 여러 시간이 다 조회되는데

user01에 대해서는 마지막 LAST_LOGIN_TIME 하나만 조회하고 싶습니다.

group by 해도 결과값이 똑같이 나오는데 조건을 어떻게 붙이면 되는지 궁금합니다.

귀한 시간에 답변 주셔셔 미리 감사 드립니다.

by 우리집아찌 [2018.08.14 14:34:24]

SELECT *

 FROM  ( SELECT  테이블.*

                    ,   ROW_NUMBER() OVER(PARTITION BY 사용자  ORDER BY LAST_LOGIN_TIME DESC ) RN 

              FROM 테이블  ) A 

WHERE RN =1  


by 시리 [2018.08.14 14:55:44]

  select
            user0_.USER_ID as col_0_0_,
            user0_.USER_NM as col_1_0_,
            dept1_.DEPT_ID as col_2_0_,
            dept1_.DEPT_NM as col_3_0_,
            agentuserl2_.HOST_NM as col_4_0_,
            agentuserl2_.MULTI_IP as col_5_0_,
            agentuserl2_.LAST_LOGIN_TIME as col_6_0_,
            agentuserl2_.LAST_LOGOUT_TIME as col_7_0_,
            agentuserl2_.LOG_DT as col_8_0_,
            agentuserl2_.VERSION as col_9_0_,
            agentuserl2_.AGENT_LOG_TYPE_CD as col_10_0_
        from
            CM_USER user0_
        inner join
            CM_DEPT dept1_
                on user0_.DEPT_ID=dept1_.DEPT_ID
        left outer join
            (
   select
    MACHINE_KEY, MULTI_IP, AGENT_LOG_TYPE_CD, LOG_DT, DS_LOG_AGENT.USER_ID, DS_LOG_AGENT.LAST_LOGIN_TIME, LAST_LOGOUT_TIME, VERSION, HOST_NM, REG_DT, REG_USER_ID, MODIFY_DT, MODIFY_USER_ID
   from
    DS_LOG_AGENT
   JOIN
   (
    SELECT
     user_id, MAX(last_login_time) AS last_login_time
    FROM
     ds_log_agent
    GROUP BY
     user_id
   ) AS TMP_DS_LOG_AGENT
    ON TMP_DS_LOG_AGENT.user_id = DS_LOG_AGENT.user_id
    AND TMP_DS_LOG_AGENT.last_login_time = DS_LOG_AGENT.last_login_time
  )agentuserl2_
          on user0_.USER_ID=agentuserl2_.USER_ID
  order by
            agentuserl2_.LOG_DT desc;
 

 

감사합니다.

이런 식으로 수정했습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입