with t1 as( select 'aa' as empno, 1 as seqno, '1111' as id1, '2222' as id2, '3333' as id3 from dual union all select 'aa' as empno, 2 as seqno, 'a111' as id1, '' as id2, '' as id3 from dual union all select 'bb' as empno, 1 as seqno, 'b123' as id1, 'c124' as id2, '' as id3 from dual union all select 'bb' as empno, 2 as seqno, '' as id1, '' as id2, '' as id3 from dual ) select z.emp_no, z.seqno, decode(z.id1, null 'N', 'Y'), decode(z.id2, null 'N', 'Y'), decode(z.id3, null 'N', 'Y'), (s1+s2+s3) tot_sum from ( select emp_no, seqno, id1, id2, id3 , sum(decode(id1, null, 0,1) ) over (partition by emp_no, seqno) as s1 , sum(decode(id2, null, 0,1) ) over (partition by emp_no, seqno) as s2 , sum(decode(id3, null, 0,1) ) over (partition by emp_no, seqno) as s3 from t1 ) z
id1,2,3 각각의 합을 구하는 쿼리를 작성했는데요
좀 더 간단하게 구할 수 있을까요?
결과를 아래와 같이 나오게 하려고 합니다.
empno | seqno | id1 | id2 | id3 | sum |
aa | 1 | Y | Y | Y | 3 |
aa | 2 | Y | N | N | 1 |
bb | 1 | Y | Y | N | 2 |
bb | 2 | N | N | N | 0 |
WITH t1 AS ( SELECT 'aa' empno, 1 seqno, '1111' id1, '2222' id2, '3333' id3 FROM dual UNION ALL SELECT 'aa', 2, 'a111', '' , '' FROM dual UNION ALL SELECT 'bb', 1, 'b123', 'c124', '' FROM dual UNION ALL SELECT 'bb', 2, '' , '' , '' FROM dual ) SELECT empno, seqno , id1, id2, id3 , NVL2(id1, 'Y', 'N') yn1 , NVL2(id2, 'Y', 'N') yn2 , NVL2(id3, 'Y', 'N') yn3 , NVL2(id1, 1, 0) + NVL2(id2, 1, 0) + NVL2(id3, 1, 0) s , DECODE('', id1, 0, id2, 1, id3, 2, 3) s -- 항상 순서대로 채워진다면? FROM t1 ORDER BY empno, seqno ;