질문좀 드립니다. A라는 테이블이 로그인아이디랑 아이피가 있습니다. 동일한 아이디에서 여러아이피로 접속하는경우 체크하는 쿼리는 어떻게 해야하나요?
DB2 는 안써봤지만 ..
MSSQL 쿼리와 유사할거라 지레짐작하고....
작성합니다.
아이디별로, 접속한 IP가 2개 이상인 경우
해당 사용자 ID만 열거하는 형태로 작성되었습니다.
--쿼리시작
With table_a As ( Select 'test1' As id, '1.1.1.1' As ip Union All Select 'test1' As id, '1.1.1.2' As ip Union All Select 'test1' As id, '1.1.1.3' As ip Union All Select 'test2' As id, '1.1.1.3' As ip Union All Select 'test2' As id, '1.1.1.3' As ip Union All Select 'test2' As id, '1.1.1.3' As ip Union All Select 'test2' As id, '1.1.1.3' As ip Union All Select 'test2' As id, '1.1.1.3' As ip Union All Select 'test2' As id, '1.1.1.3' As ip Union All Select 'test3' As id, '1.1.1.5' As ip ) Select a.id From ( Select a.id ,a.ip From table_a a Group By a.id ,a.ip ) a Group By a.id Having COUNT(a.ip) > 1
--결과
---ID----
test1
----------