WITH t0(dept, cnt) AS
( -- 0. 정원 테이블 --
SELECT 'A01', 4 FROM dual
UNION ALL SELECT 'B01', 3 FROM dual
UNION ALL SELECT 'C01', 3 FROM dual
)
, t(id, point, app_1, app_2, app_3) AS
( -- 0. 지망 테이블 --
SELECT '0001', '90', 'B01', 'A01', 'C01' FROM dual
UNION ALL SELECT '0002', '89', 'B01', 'A01', 'C01' FROM dual
UNION ALL SELECT '0003', '87', 'C01', 'B01', 'B01' FROM dual
UNION ALL SELECT '0004', '87', 'A01', 'C01', 'B01' FROM dual
UNION ALL SELECT '0005', '84', 'B01', 'A01', 'C01' FROM dual
UNION ALL SELECT '0006', '82', 'A01', 'A01', 'B01' FROM dual
UNION ALL SELECT '0007', '81', 'B01', 'C01', 'A01' FROM dual
UNION ALL SELECT '0008', '80', 'A01', 'A01', 'A01' FROM dual
UNION ALL SELECT '0009', '77', 'B01', 'B01', 'B01' FROM dual
UNION ALL SELECT '0010', '76', 'A01', 'A01', 'B01' FROM dual
UNION ALL SELECT '0011', '73', 'B01', 'A01', 'C01' FROM dual
UNION ALL SELECT '0012', '71', 'C01', 'A01', 'B01' FROM dual
)
, t1(dept, cnt, id, point, app_1, app_2, app_3, rn) AS
( -- 1. 1지망 선발 --
SELECT *
  FROM t0 a
     , (SELECT id, point, app_1, app_2, app_3
             , ROW_NUMBER() OVER(PARTITION BY app_1 ORDER BY point DESC) rn
          FROM t
        ) b
 WHERE a.dept = b.app_1(+)
   AND a.cnt >= b.rn(+)
)
, t2(dept, cnt, id, point, app_1, app_2, app_3, rn) AS
( -- 2. 2지망 선발 --
SELECT *
  FROM (SELECT dept, cnt - COUNT(id) cnt
          FROM t1
         GROUP BY dept, cnt
         HAVING cnt > COUNT(id)
        ) a
     , (SELECT id, point, app_1, app_2, app_3
             , ROW_NUMBER() OVER(PARTITION BY app_2 ORDER BY point DESC) rn
          FROM t
         WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE id = t.id)
        ) b
 WHERE a.dept = b.app_2(+)
   AND a.cnt >= b.rn(+)
)
, t3(dept, cnt, id, point, app_1, app_2, app_3, rn) AS
( -- 3. 3지망 선발 --
SELECT *
  FROM (SELECT dept, cnt - COUNT(id) cnt
          FROM t2
         GROUP BY dept, cnt
         HAVING cnt > COUNT(id)
        ) a
     , (SELECT id, point, app_1, app_2, app_3
             , ROW_NUMBER() OVER(PARTITION BY app_3 ORDER BY point DESC) rn
          FROM t
         WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE id = t.id)
           AND NOT EXISTS (SELECT 1 FROM t2 WHERE id = t.id)
        ) b
 WHERE a.dept = b.app_3(+)
   AND a.cnt >= b.rn(+)
)
, t4(dept, cnt, id, point, app_1, app_2, app_3, rn) AS
( -- 4. 탈락 --
SELECT null dept, null cnt
     , id, point, app_1, app_2, app_3
     , null rn
  FROM t
 WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE id = t.id)
   AND NOT EXISTS (SELECT 1 FROM t2 WHERE id = t.id)
   AND NOT EXISTS (SELECT 1 FROM t3 WHERE id = t.id)
)
SELECT id, point, app_1, app_2, app_3, dept FROM t1 WHERE id IS NOT NULL
 UNION ALL
SELECT id, point, app_1, app_2, app_3, dept FROM t2 WHERE id IS NOT NULL
 UNION ALL
SELECT id, point, app_1, app_2, app_3, dept FROM t3 WHERE id IS NOT NULL
 UNION ALL
SELECT id, point, app_1, app_2, app_3, dept FROM t4
 ORDER BY id
;

- 생각해 보세요 -
 동점자 처리 기준이 없네요?
 동순위가 발생되지 않도록 해야 합니다.
 순위를 명확하게 가릴 2차 정렬기준이 있어야만 합니다.

 마농님 긴 답글 감사합니다.

말씀하신것 처럼 동점자가 발생할 수 있습니다. 물론 정렬도 필요하고요.

제가 해결하지 못한 문제는 0011번 지원자와 0012번 지원자의 선발결과 입니다.

점수 순으로 배정을 해야 하다 보니 0011번의 3지망이 0012번의 1지망 보다 우선되어져서

0011번에게 C01이 배정되고 0012번은 탈락 되었습니다.

점수 순으로 (ROW별) 지망의 선발을 결정해야 되어서 SQL작성이 쉽지 않았습니다.

저도 마농님이 작성하신 결과와 동일한 결과만 나오더군요.

SELECT 쿼리로 결과를 만드는게 가능할까요??

WITH t0(dep1, cnt1, dep2, cnt2, dep3, cnt3) AS
( -- 0. 정원 테이블 --
SELECT 'A01', 4, 'B01', 3, 'C01', 3 FROM dual
)
, t(id, point, app_1, app_2, app_3) AS
( -- 0. 지망 테이블 --
SELECT '0001', '90', 'B01', 'A01', 'C01' FROM dual
UNION ALL SELECT '0002', '89', 'B01', 'A01', 'C01' FROM dual
UNION ALL SELECT '0003', '87', 'C01', 'B01', 'B01' FROM dual
UNION ALL SELECT '0004', '87', 'A01', 'C01', 'B01' FROM dual
UNION ALL SELECT '0005', '84', 'B01', 'A01', 'C01' FROM dual
UNION ALL SELECT '0006', '82', 'A01', 'A01', 'B01' FROM dual
UNION ALL SELECT '0007', '81', 'B01', 'C01', 'A01' FROM dual
UNION ALL SELECT '0008', '80', 'A01', 'A01', 'A01' FROM dual
UNION ALL SELECT '0009', '77', 'B01', 'B01', 'B01' FROM dual
UNION ALL SELECT '0010', '76', 'A01', 'A01', 'B01' FROM dual
UNION ALL SELECT '0011', '73', 'B01', 'A01', 'C01' FROM dual
UNION ALL SELECT '0012', '71', 'C01', 'A01', 'B01' FROM dual
)
, t1 AS
( -- 1. 순위 부여 --
SELECT id, point, app_1, app_2, app_3
     , ROW_NUMBER() OVER(ORDER BY point DESC, id) rn
  FROM t
)
, t2( id, point, app_1, app_2, app_3, rn
    , dep1, cnt1, dep2, cnt2, dep3, cnt3
    , pass) AS
( -- 2. Recursive SQL 을 통한 정원 차감 --
SELECT id, point, app_1, app_2, app_3, rn
     , dep1, DECODE(app_1, dep1, cnt1-1, cnt1) cnt1
     , dep2, DECODE(app_1, dep2, cnt2-1, cnt2) cnt2
     , dep3, DECODE(app_1, dep3, cnt3-1, cnt3) cnt3
     , app_1 pass
  FROM t1
     , t0
 WHERE t1.rn = 1
 UNION ALL
SELECT c.id, c.point, c.app_1, c.app_2, c.app_3, c.rn
     , dep1
     , DECODE(CASE WHEN cnt1 > 0 AND c.app_1 = dep1 THEN c.app_1
                   WHEN cnt2 > 0 AND c.app_1 = dep2 THEN c.app_1
                   WHEN cnt3 > 0 AND c.app_1 = dep3 THEN c.app_1
                   WHEN cnt1 > 0 AND c.app_2 = dep1 THEN c.app_2
                   WHEN cnt2 > 0 AND c.app_2 = dep2 THEN c.app_2
                   WHEN cnt3 > 0 AND c.app_2 = dep3 THEN c.app_2
                   WHEN cnt1 > 0 AND c.app_3 = dep1 THEN c.app_3
                   WHEN cnt2 > 0 AND c.app_3 = dep2 THEN c.app_3
                   WHEN cnt3 > 0 AND c.app_3 = dep3 THEN c.app_3
                   ELSE Null END, dep1, cnt1-1, cnt1) cnt1
     , dep2
     , DECODE(CASE WHEN cnt1 > 0 AND c.app_1 = dep1 THEN c.app_1
                   WHEN cnt2 > 0 AND c.app_1 = dep2 THEN c.app_1
                   WHEN cnt3 > 0 AND c.app_1 = dep3 THEN c.app_1
                   WHEN cnt1 > 0 AND c.app_2 = dep1 THEN c.app_2
                   WHEN cnt2 > 0 AND c.app_2 = dep2 THEN c.app_2
                   WHEN cnt3 > 0 AND c.app_2 = dep3 THEN c.app_2
                   WHEN cnt1 > 0 AND c.app_3 = dep1 THEN c.app_3
                   WHEN cnt2 > 0 AND c.app_3 = dep2 THEN c.app_3
                   WHEN cnt3 > 0 AND c.app_3 = dep3 THEN c.app_3
                   ELSE Null END, dep2, cnt2-1, cnt2) cnt2
     , dep3
     , DECODE(CASE WHEN cnt1 > 0 AND c.app_1 = dep1 THEN c.app_1
                   WHEN cnt2 > 0 AND c.app_1 = dep2 THEN c.app_1
                   WHEN cnt3 > 0 AND c.app_1 = dep3 THEN c.app_1
                   WHEN cnt1 > 0 AND c.app_2 = dep1 THEN c.app_2
                   WHEN cnt2 > 0 AND c.app_2 = dep2 THEN c.app_2
                   WHEN cnt3 > 0 AND c.app_2 = dep3 THEN c.app_2
                   WHEN cnt1 > 0 AND c.app_3 = dep1 THEN c.app_3
                   WHEN cnt2 > 0 AND c.app_3 = dep2 THEN c.app_3
                   WHEN cnt3 > 0 AND c.app_3 = dep3 THEN c.app_3
                   ELSE Null END, dep3, cnt3-1, cnt3) cnt3
     , CASE WHEN cnt1 > 0 AND c.app_1 = dep1 THEN c.app_1
            WHEN cnt2 > 0 AND c.app_1 = dep2 THEN c.app_1
            WHEN cnt3 > 0 AND c.app_1 = dep3 THEN c.app_1
            WHEN cnt1 > 0 AND c.app_2 = dep1 THEN c.app_2
            WHEN cnt2 > 0 AND c.app_2 = dep2 THEN c.app_2
            WHEN cnt3 > 0 AND c.app_2 = dep3 THEN c.app_2
            WHEN cnt1 > 0 AND c.app_3 = dep1 THEN c.app_3
            WHEN cnt2 > 0 AND c.app_3 = dep2 THEN c.app_3
            WHEN cnt3 > 0 AND c.app_3 = dep3 THEN c.app_3
            ELSE Null END pass
  FROM t2 p
     , t1 c
 WHERE p.rn + 1 = c.rn
)
SELECT id, point, app_1, app_2, app_3, pass
  FROM t2
;

보통 지망을 우선하고 점수를 차선으로 하는데...
이 경우는 점수가 우선이고 지망이 차선인 경우네요...
점수순으로 루프 돌려가며 정원을 차감해가는 로직입니다.
SELECT 만으로 루프를 구현하기는 힘드네요.
일단 만들긴 했습니다.
11G 의 Recursive SQL 로 구현했습니다.
부서가 3개 뿐이 아니라면? 수정 노가다가 필요하겠네요.
PL/SQL 을 이용하여 업데이트 쳐가면서 루프돌리는게 나을 것 같습니다.
알고리즘은 동일합니다.
1. 점수순서대로 루프
2. 지망순서대로 정원이 남아 있는 부서 선택(지망테이블 선택부서 업데이트)
3. 정원 차감(정원 테이블 정원 차감)

마농님 답변 감사합니다.

작성해 주신 SQL도 도움이 많이 되었습니다.

제 경우에는 여러모로 검토를 해보아도 PL/SQL로 처리하는 것이 좀더 나은 방법인걸로 판단됩니다.

감사합니다.