데이터베이스 질문과 답변
데이터
| 사원번호 | 순번 | 다음순번 |
| 12801186 | 1 | 2 |
| 12507557 | 2 | 3 |
| 12507558 | 3 | 4 |
| 12507526 | 4 | 1 |
결과
| 사원번호 | 순번 | 다음순번 | 다음사원번호 |
| 12801186 | 1 | 2 | 12507557 |
| 12507557 | 2 | 3 | 12507558 |
| 12507558 | 3 | 4 | 12507526 |
| 12507526 | 4 | 1 | 12801186 |
위와 같은 결과 를 구하려고 하는데 쿼리 조언을 구합니다.
by 아린
[2013.11.15 11:36:00]
WITH t(사원번호, 순번, 다음순번) AS(
SELECT 12801186, 1, 2 FROM dual UNION ALL
SELECT 12507557, 2, 3 FROM dual UNION ALL
SELECT 12507558, 3, 4 FROM dual UNION ALL
SELECT 12507526, 4, 1 FROM dual
)
SELECT 사원번호, 순번, 다음순번
, (SELECT 사원번호
FROM t t2
WHERE t2.순번 = t1.다음순번) 다음사원번호
FROM t t1
---
---
SELECT t1.사원번호, t1.순번, t1.다음순번
WITH T AS (
SELECT '12801186' EMPNO , 1 SEQ ,2 NEXT_SEQ FROM DUAL UNION ALL
SELECT '12507557' , 2 ,3 FROM DUAL UNION ALL
SELECT '12507558' , 3 ,4 FROM DUAL UNION ALL
SELECT '12507526' , 4 ,1 FROM DUAL
)
-- 서브 쿼리
SELECT EMPNO
, SEQ
, NEXT_SEQ
, nvl(lead(empno) over(order by seq), first_value(empno) over(order by seq)) n_empno
FROM T
/
, t2.사원번호 다음사원번호
FROM t t1, t t2
WHERE t1.다음순번 = t2.순번
ORDER BY t1.순번
by 우리집아찌
[2013.11.15 11:40:45]
WITH T AS ( SELECT '12801186' EMPNO , 1 SEQ ,2 NEXT_SEQ FROM DUAL UNION ALL SELECT '12507557' , 2 ,3 FROM DUAL UNION ALL SELECT '12507558' , 3 ,4 FROM DUAL UNION ALL SELECT '12507526' , 4 ,1 FROM DUAL ) -- 서브 쿼리 SELECT EMPNO , SEQ , NEXT_SEQ , (SELECT EMPNO FROM T a WHERE a.SEQ = T.NEXT_SEQ) NEXT_EMPNO FROM T -- SELF JOIN SELECT a.EMPNO , a.SEQ , a.NEXT_SEQ , b.EMPNO NEXT_EMPNO FROM T a , T b WHERE a.NEXT_SEQ = b.SEQ ORDER BY a.SEQ
by feelie
[2013.11.15 13:02:38]
WITH T AS (
SELECT '12801186' EMPNO , 1 SEQ ,2 NEXT_SEQ FROM DUAL UNION ALL
SELECT '12507557' , 2 ,3 FROM DUAL UNION ALL
SELECT '12507558' , 3 ,4 FROM DUAL UNION ALL
SELECT '12507526' , 4 ,1 FROM DUAL
)
-- 서브 쿼리
SELECT EMPNO
, SEQ
, NEXT_SEQ
, nvl(lead(empno) over(order by seq), first_value(empno) over(order by seq)) n_empno
FROM T
/
) 버튼을 클릭하여 작성 하시면 됩니다.