데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

고수님들께 Query 질문 드립니다.

작성자

김창기(yaksok43)

작성일

2011-09-01 14:50ⓒ
2011-09-01 17:32ⓜ

조회수

4,254

SELECT

ID, NAME , COUNT(*) AS CNT

FROM A

GROUP BY ID, NAME

이런걸 원하신건가요?

이태규(xorb25)님이 2011-09-01 18:10에 작성한 댓글입니다.

답변 감사드립니다~ ^^;

아래 결과를 원했습니다~

해결하는데 많은 도움 주셔서 감사합니다...

WITH t AS
(
SELECT '1000' ID, 'A' NAME, 'XXX' key FROM dual UNION ALL
SELECT '1001' ID, 'R' NAME, 'XXX' key FROM dual UNION ALL
SELECT '1002' ID, 'C' NAME, 'XXX' key FROM dual UNION ALL
SELECT '1003' ID, 'D' NAME, 'XXX' key FROM dual UNION ALL
SELECT '1004' ID, 'F' NAME, 'XXX' key FROM dual UNION ALL
SELECT '1005' ID, 'G' NAME, 'XXX' key FROM dual UNION ALL
SELECT '1000' ID, 'A' NAME, 'YYY' key FROM dual UNION ALL
SELECT '1001' ID, 'B' NAME, 'YYY' key FROM dual UNION ALL
SELECT '1002' ID, 'K' NAME, 'YYY' key FROM dual UNION ALL
SELECT '1003' ID, 'D' NAME, 'YYY' key FROM dual UNION ALL
SELECT '1004' ID, 'F' NAME, 'YYY' key FROM dual UNION ALL
SELECT '1005' ID, 'G' NAME, 'YYY' key FROM dual UNION ALL
SELECT '1000' ID, 'A' NAME, 'ZZZ' key FROM dual UNION ALL
SELECT '1001' ID, 'B' NAME, 'ZZZ' key FROM dual UNION ALL
SELECT '1002' ID, 'C' NAME, 'ZZZ' key FROM dual UNION ALL
SELECT '1003' ID, 'D' NAME, 'ZZZ' key FROM dual UNION ALL
SELECT '1004' ID, 'F' NAME, 'ZZZ' key FROM dual UNION ALL
SELECT '1005' ID, 'G' NAME, 'ZZZ' key FROM dual UNION ALL
SELECT '1000' ID, 'A' NAME, 'AAA' key FROM dual UNION ALL
SELECT '1001' ID, 'B' NAME, 'AAA' key FROM dual UNION ALL
SELECT '1002' ID, 'C' NAME, 'AAA' key FROM dual UNION ALL
SELECT '1003' ID, 'D' NAME, 'AAA' key FROM dual
)
SELECT id,name,count(*)
FROM t
group by id,name having count(*) = 4
;