데이터베이스 질문과 답변
-테이블
| dept_code(부서코드 | id (아이디) | salary (연봉) |
| 001 | ID1 | 3000 |
| 001 | ID2 | 4000 |
| 002 | ID3 | 3500 |
| 003 | ID4 | 4000 |
001, 002, 003 각각 부서의 연봉 합계를 구하는것이 아닌
001, 002+003 부서의 연봉 합계를 구하는 방법이 궁급합니다.
(001부서의 연봉 합계, (002, 003)의 부서의 연봉 합계)
-결과 테이블
| dept (부서) | salary (연봉합계) |
| 001 | 7000 |
| 002+003(기타부서) | 7500 |
매번 도와주셔서 감사합니다.
by jkson
[2021.08.26 14:40:04]
with t as
(
select '001' as dept_code, 'ID1' as id, 3000 as salary from dual union all
select '001' as dept_code, 'ID2' as id, 4000 as salary from dual union all
select '002' as dept_code, 'ID3' as id, 3500 as salary from dual union all
select '003' as dept_code, 'ID4' as id, 4000 as salary from dual
)
select decode(dept_code,'001','001','기타부서') as dept
, sum(salary) as sal
from t
group by decode(dept_code,'001','001','기타부서')
order by 1
by jkson
[2021.08.26 14:59:09]
예시 출력형식대로..
with t as
(
select '001' as dept_code, 'ID1' as id, 3000 as salary from dual union all
select '001' as dept_code, 'ID2' as id, 4000 as salary from dual union all
select '002' as dept_code, 'ID3' as id, 3500 as salary from dual union all
select '003' as dept_code, 'ID4' as id, 4000 as salary from dual
)
select regexp_replace(listagg(dept_code,'+') within group (order by dept_code),'([^+]+)(\+\1)+','\1') || decode(dept_code,'001',null,'(기타부서)') as dept
, sum(salary) as sal
from t
group by decode(dept_code,'001',null,'(기타부서)')
order by 1
by 벌댕
[2021.08.26 16:27:37]
작성해주신거 참고해서 적용시켰습니다.
group by 에 decode를 사용할수 있는지 꿈에도 몰랐네요
배울게 많습니다 감사합니다!!
by jkson
[2021.08.27 06:54:42]
regexp_replace(listagg(dept_code,'+') within group (order by dept_code),'([^+]+)(\+\1)+','\1')
->
19c부터는 간단히 listagg(distinct dept_code,'+') within group (order by dept_code)
로 처리하면 된다고 하네요.
오래 공부를 안 했더니..;
by 벌댕
[2021.08.27 11:15:30]
추가 답변 감사합니다!
tibero를 사용중이라 decode로 적용시켰고
aggr_concat으로도 한번 시도해보겠습니다ㅎㅎ
) 버튼을 클릭하여 작성 하시면 됩니다.