데이터베이스 질문과 답변
안녕하세요.
중복 데이터 처리 건에 대해 질문 드립니다!
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 해도 결과값이 똑같이 나오는데 조건을 어떻게 붙이면 되는지 궁금합니다.
귀한 시간에 답변 주셔셔 미리 감사 드립니다.
SELECT *
FROM ( SELECT 테이블.*
, ROW_NUMBER() OVER(PARTITION BY 사용자 ORDER BY LAST_LOGIN_TIME DESC ) RN
FROM 테이블 ) A
WHERE RN =1
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;
감사합니다.
이런 식으로 수정했습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.