예1) | 예2) | ||||||
RN | GP | 순서 | RN | GP | 순서 | ||
1 | A | T1 | 1 | A | T1 | ||
2 | A | T2 | 2 | A | T2 | ||
3 | A | T1 | 3 | A | T3 | ||
4 | A | T2 | 4 | A | T1 | ||
5 | A | T1 | 5 | A | T2 | ||
6 | A | T2 | 6 | A | T3 | ||
1 | B | T2 | 1 | B | T2 | ||
2 | B | T1 | 2 | B | T3 | ||
3 | B | T2 | 3 | B | T1 | ||
4 | B | T1 | 4 | B | T2 | ||
5 | B | T2 | 5 | B | T3 | ||
6 | B | T1 | 6 | B | T1 | ||
1 | C | T1 | 1 | C | T3 | ||
2 | C | T2 | 2 | C | T2 | ||
3 | C | T1 | 3 | C | T1 | ||
4 | C | T2 | 4 | C | T3 | ||
5 | C | T1 | 5 | C | T1 | ||
6 | C | T2 | 6 | C | T2 | ||
RN 그룹으로 반복될때 마다 순서가 한번식 바뀔수 있는지.. 순서가 2개가 있는경우 T1 T2 / T2 T1 / T1 T2 이런식이고 순서가 3개 있는 경우 T1 T2 T3 / T2 T3 T1 / T3 T2 T1 / T1 T2 T3 / ....... 가능한지요? 아무리 해봐도 안나옵니다. ㅜㅜ
with t |
WITH t AS ( SELECT 'A' gp FROM dual UNION ALL SELECT 'A' FROM dual UNION ALL SELECT 'A' FROM dual UNION ALL SELECT 'A' FROM dual UNION ALL SELECT 'A' FROM dual UNION ALL SELECT 'A' FROM dual UNION ALL SELECT 'B' FROM dual UNION ALL SELECT 'B' FROM dual UNION ALL SELECT 'B' FROM dual UNION ALL SELECT 'B' FROM dual UNION ALL SELECT 'B' FROM dual UNION ALL SELECT 'B' FROM dual UNION ALL SELECT 'C' FROM dual UNION ALL SELECT 'C' FROM dual UNION ALL SELECT 'C' FROM dual UNION ALL SELECT 'C' FROM dual UNION ALL SELECT 'C' FROM dual UNION ALL SELECT 'C' FROM dual ) SELECT rn , gp , 'T' || (MOD(rn + dr - 2, n) + 1) st FROM (SELECT gp , DENSE_RANK() OVER(ORDER BY gp) dr , ROW_NUMBER() OVER(PARTITION BY gp ORDER BY 1) rn , 3 n -- 테스트 값 조정 FROM t ) ;
본문 내용하고 다른 것 같은데.. 뭔가 새로운 값을 만들어 내는 것이 아니라 말씀하신 순서대로 sort를 원하시는 것 맞나요?
with t as ( select '1' RN , 'A' GP, 'T1' ST from dual union all select '2' , 'A', 'T2' from dual union all select '3' , 'A', 'T1' from dual union all select '4' , 'A', 'T2' from dual union all select '5' , 'A', 'T1' from dual union all select '6' , 'A', 'T2' from dual union all select '1' , 'B', 'T1' from dual union all select '2' , 'B', 'T2' from dual union all select '3' , 'B', 'T1' from dual union all select '4' , 'B', 'T2' from dual union all select '5' , 'B', 'T1' from dual union all select '6' , 'B', 'T2' from dual union all select '1' , 'C', 'T1' from dual union all select '2' , 'C', 'T2' from dual union all select '3' , 'C', 'T1' from dual union all select '4' , 'C', 'T2' from dual union all select '5' , 'C', 'T1' from dual union all select '6' , 'C', 'T2' from dual ) select rn, gp, st from ( select rn, gp, st , dense_rank() over(order by gp) gprank , count(1) over (partition by gp) gpcnt from t ) order by gp, decode(mod(rn + gprank - 1, gpcnt),0,gpcnt,mod(rn + gprank - 1, gpcnt))