NTILE PARTITION BY

 

 

SELECT CASE WHEN RN2 = 1 THEN 'A조'
            WHEN RN2 = 2 THEN 'B조'
            WHEN RN2 = 3 THEN 'C조'
			      END AS NEW_TEAM , T2.*
FROM (SELECT NTILE(3) OVER (PARTITION BY TEAM_NM ORDER BY DBMS_RANDOM.RANDOM) AS RN2,
             TEAM_NM, SAWON_CODE,SAWON_NAME,INSERT_DATE
FROM L_TAB
WHERE TO_CHAR(INSERT_DATE,'YYYYMM') = TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')
ORDER BY RN2, TEAM_NM
) T2

 

 

기존 팀 [TEAM_NM] 에서 팀별 3명 씩 나누어 새로운 팀 [NEW_TEAM] 에 랜덤 배정