데이터베이스 질문과 답변
name, date, val,
사원1 2018-01-01 0
사원1 2018-01-01 1
사원1 2018-02-01 1
사원2 2018-01-01 2
사원2 2018-03-01 2
사원3 2018-03-01 3
사원3 2018-03-01 2
사원4 2018-01-01 3
사원5 2018-02-01 1
다음과 같은 테이블에서 name과 date가 같고 2개 이상의 row를 가진 값들만 조회하려고 합니다.
<결과>
사원1 2018-01-01 0
사원1 2018-01-01 1
사원3 2018-03-01 3
사원3 2018-03-01 2
이렇게 나오도록 하려면 어떻게 할지 조언을 구합니다.
by 우리집아찌
[2018.07.18 18:10:23]
WITH T ( NM , DT , VAL ) AS (
SELECT '사원1' , '2018-01-01' ,0 FROM DUAL UNION ALL
SELECT '사원1' , '2018-01-01' ,1 FROM DUAL UNION ALL
SELECT '사원1' , '2018-02-01' ,1 FROM DUAL UNION ALL
SELECT '사원2' , '2018-01-01' ,2 FROM DUAL UNION ALL
SELECT '사원2' , '2018-03-01' ,2 FROM DUAL UNION ALL
SELECT '사원3' , '2018-03-01' ,3 FROM DUAL UNION ALL
SELECT '사원3' , '2018-03-01' ,2 FROM DUAL UNION ALL
SELECT '사원4' , '2018-01-01' ,3 FROM DUAL UNION ALL
SELECT '사원5' , '2018-02-01' ,1 FROM DUAL
)
SELECT A.*
FROM T A
, ( SELECT NM , DT FROM T GROUP BY NM , DT HAVING COUNT(*) >= 2 ) B
WHERE A.NM = B.NM
AND A.DT = B.DT
by OMIL
[2018.07.18 18:49:00]
답변 감사합니다.
by 마농
[2018.07.18 18:57:26]
SELECT nm, dt, val
FROM (SELECT nm, dt, val
, COUNT(*) OVER(PARTITION BY nm, dt) cnt
FROM t a
)
WHERE cnt > 1
;
SELECT *
FROM t a
WHERE EXISTS (SELECT 1
FROM t b
WHERE b.nm = a.nm
AND b.dt = a.dt
AND b.val != a.val
)
;
) 버튼을 클릭하여 작성 하시면 됩니다.