select
no -- 번호,
dgree-- 차수,
city-- 지역번호,
case when count(1) > 1 then max( case when city ='01' then '서울'
when city ='02' then '대전'
when city = '03' then '대구'
when city = '04' then '부산' end )
|| decode(count(1), 1,'','외' || (count(1)-1) || '건') end 'cityName'
from table1
group by no, dgree, city
이렇게 쿼리를 작성하게 되면 차수의 최대값으로만 표기 되는데, 차수의 도시의 최대값이 아닌, 각 차수의 첫번째 값의 @로 표현하고 싶습니다.
어떻게 표현을 해야 좋을지 잘 몰라 질의 드립니다...
결과 값
결과 값
차수 text
001 서울 외 3
002 대전 외 1
003 서울
비슷하긴 한데..코드 순서를 오름차순으로 했습니다.
with t (no, dgree, city) as ( select '1111', '001', '01' from dual union all select '1111', '001', '02' from dual union all select '1111', '001', '03' from dual union all select '1111', '001', '04' from dual union all select '1111', '002', '02' from dual union all select '1111', '002', '03' from dual union all select '1111', '003', '01' from dual ) select no, dgree, case when cnt > 1 then city||'외'||(cnt-1) else city end city from ( select no, dgree, cnt, decode(city, '01', '서울', '02', '대전', '03', '대구', '04', '부산') city from ( select no, dgree, city, count(city) over (partition by no, dgree) cnt, row_number() over (partition by no, dgree order by city) rn from t ) where rn = 1 )
WITH table1 AS ( SELECT '1111' no, '001' dgree, '01' city FROM dual UNION ALL SELECT '1111', '001', '02' FROM dual UNION ALL SELECT '1111', '001', '03' FROM dual UNION ALL SELECT '1111', '001', '04' FROM dual UNION ALL SELECT '1111', '002', '02' FROM dual UNION ALL SELECT '1111', '002', '03' FROM dual UNION ALL SELECT '1111', '003', '01' FROM dual ) SELECT no , dgree , DECODE(MIN(city), '01', '서울', '02', '대전', '03', '대구', '04', '부산') || DECODE(COUNT(*), 1, '', ' 외 ' || (COUNT(*)-1) || '건') cityName FROM table1 GROUP BY no, dgree ORDER BY no, dgree ;