GROUP BY 병록번호, 오더코드 HAVING COUNT(*) > 1

아.. 아니다.. ㅋ 무시 하세요..

어.. 아찌님 지웠다.. 아찌님게 맞는거 같았는데..

-- 민망하지만 다시한번..
WITH T (c1,c2) AS (
SELECT '0000001' , 'HE111G' FROM DUAL UNION ALL
SELECT '0000001' , 'HE115G' FROM DUAL UNION ALL
SELECT '0000002' , 'HE111G' FROM DUAL UNION ALL
SELECT '0000003' , 'HE115G' FROM DUAL UNION ALL
SELECT '0000003' , 'HE120R' FROM DUAL 
)

SELECT * FROM T
WHERE c1 IN (SELECT c1 FROM T
       GROUP BY c1 HAVING COUNT(c1) > 1)

/*------------------------------------------------------*/

SELECT * FROm
(SELECT T.* , COUNT(*) OVER(PARTITION BY c1 ) cnt FROM T) 
WHERE cnt > 1

--저도 잘 이해한건지 모르겠지만.. 다시 한번.(아찌님 데이타셋 이용.. 호호호)
--이해 내용은 병록번호 기준, 오더코드가 중복되지 1건 이상 있는 것만 출력되는 쪽으로..
WITH T (c1,c2) AS (
 SELECT '0000001' , 'HE111G' FROM DUAL UNION ALL
 SELECT '0000001' , 'HE115G' FROM DUAL UNION ALL
 SELECT '0000002' , 'HE111G' FROM DUAL UNION ALL
 SELECT '0000003' , 'HE115G' FROM DUAL UNION ALL
 SELECT '0000003' , 'HE120R' FROM DUAL UNION ALL
 SELECT '0000004' , 'HE115G' FROM DUAL UNION ALL
 SELECT '0000004' , 'HE115G' FROM DUAL 
)
SELECT * FROM (
 SELECT
 c1, c2, DENSE_RANK() OVER(PARTITION BY c1 ORDER BY c1, c2) AS CNT
 FROM
 T
)
WHERE 
 CNT > 1

with t as (
 select '00000001' as c1, 'HE11G...' as c2, '처방이름' as c3 from dual union all
 select '00000001', 'HE15G...', '처방이름' from dual union all
 select '00000002', 'HE11G...', '처방이름' from dual union all
 select '00000003', 'HE15G...', '처방이름' from dual union all
 select '00000003', 'HE20G...', '처방이름' from dual
)
select
 aa, bb, cc
from (select 
 c3 as aa, count(c1) as bb, c2 as cc
 from t
 group by c2, c3
 ) a
where bb > 1