데이터베이스 질문과 답변
SELECT * FROM users GROUP BY username,birth HAVING COUNT(*) > 1
홍길동 1950-04-01
홍길동 1950-04-01
테이블에서 중복데이터를 찾으려고 하는데
위 쿼리로 하니
홍길동 1950-04-01 값이 하나만 나오는데
전부다 나오게 할수있나요..?
by 우리집아찌
[2017.12.12 09:39:27]
WITH users ( username,birth ) AS (
SELECT 'A' , '20170101' FROM DUAL UNION ALL
SELECT 'A' , '20170101' FROM DUAL UNION ALL
SELECT 'B' , '20170101' FROM DUAL UNION ALL
SELECT 'B' , '20170102' FROM DUAL UNION ALL
SELECT 'C' , '20170103' FROM DUAL
)
SELECT A.username
, B.birth
FROM users A
, (SELECT * FROM users GROUP BY username,birth HAVING COUNT(*) > 1 ) B
WHERE A.username = B.username
AND A.birth = B.birth
by 마농
[2017.12.12 09:46:56]
SELECT a.*
FROM users a
INNER JOIN
(SELECT username, birth
FROM users
GROUP BY username, birth
HAVING COUNT(*) > 1
) b
ON a.username = b.username
AND a.birth = b.birth
;
SELECT a.*
FROM users a
WHERE EXISTS (SELECT 1
FROM users b
WHERE b.username = a.username
AND b.birth = a.birth
AND b.idx != a.idx -- PK
)
;
by 고수가되고싶어요
[2017.12.12 10:54:26]
WITH users ( username,birth ) AS (
SELECT 'A' , '20170101' FROM DUAL UNION ALL
SELECT 'A' , '20170101' FROM DUAL UNION ALL
SELECT 'B' , '20170101' FROM DUAL UNION ALL
SELECT 'B' , '20170102' FROM DUAL UNION ALL
SELECT 'B' , '20170102' FROM DUAL UNION ALL
SELECT 'C' , '20170103' FROM DUAL
)
SELECT * FROM users a
WHERE EXISTS ( SELECT 1
FROM users b
WHERE a.username = b.username
AND a.birth = b.birth
GROUP BY username,birth
HAVING COUNT(*) > 1)
by 밍밍밍밍
[2017.12.12 11:59:37]
답변주신분들 감사합니다
해결했습니다~
) 버튼을 클릭하여 작성 하시면 됩니다.