NTILE이라는 그룹함수는 특정 컬럼을 n개만큼 구분해 준다.
WITH T AS (
SELECT '2014125' EMP_NO FROM DUAL UNION ALL
SELECT '2014455' EMP_NO FROM DUAL UNION ALL
SELECT '2014532' EMP_NO FROM DUAL UNION ALL
SELECT '2014234' EMP_NO FROM DUAL UNION ALL
SELECT '2014765' EMP_NO FROM DUAL UNION ALL
SELECT '2014321' EMP_NO FROM DUAL UNION ALL
SELECT '2014012' EMP_NO FROM DUAL UNION ALL
SELECT '2014056' EMP_NO FROM DUAL UNION ALL
SELECT '2014089' EMP_NO FROM DUAL UNION ALL
SELECT '2014022' EMP_NO FROM DUAL UNION ALL
SELECT '2014044' EMP_NO FROM DUAL UNION ALL
SELECT '2014055' EMP_NO FROM DUAL UNION ALL
SELECT '2014011' EMP_NO FROM DUAL UNION ALL
SELECT '2014442' EMP_NO FROM DUAL UNION ALL
SELECT '2014232' EMP_NO FROM DUAL
)
SELECT EMP_NO
, NTILE(3) OVER ( ORDER BY EMP_NO) GRP3
, NTILE(4) OVER ( ORDER BY EMP_NO) GRP4
, NTILE(5) OVER ( ORDER BY EMP_NO) GRP5
, NTILE(6) OVER ( ORDER BY EMP_NO) GRP6
FROM T
실행결과는..
2014011 1 1 1 1
2014012 1 1 1 1
2014022 1 1 1 1
2014044 1 1 2 2
2014055 1 2 2 2
2014056 2 2 2 2
2014089 2 2 3 3
2014125 2 2 3 3
2014232 2 3 3 3
2014234 2 3 4 4
2014321 3 3 4 4
2014442 3 3 4 5
2014455 3 4 5 5
2014532 3 4 5 6
2014765 3 4 5 6
'sql' 카테고리의 다른 글
| Oracle hr 확인 (0) | 2015.03.06 |
|---|---|
| 오라클 월 마지막날 첫째날 구하기 (0) | 2014.09.12 |
| sqlite에서 rownum기능.. LIMIT (0) | 2014.07.31 |
| yyyymmdd를 종종 yyyy-mm-dd로 표현해야 할때.. (0) | 2014.07.17 |
| NULL값 정렬 order by nulls first, last (0) | 2014.04.01 |
