with a(key,name) as(
  select 1,'C' from dual
  union all
  select 2,'A' from dual
  union all
  select 3,'B' from dual
  union all
  select 4,'A' from dual)
  ,b(key,name) as(
  select 1,'C' from dual
  union all
  select 2,'A' from dual
  union all
  select 3,'B' from dual
  union all
  select 4,'A' from dual
   union all
  select 5,'A' from dual
  union all
  select 6,'B' from dual
  union all
  select 7,'C' from dual
  union all
  select 8,'D' from dual
  union all
  select 9,'E' from dual)
  select key,name,case when cnt2=1 or cnt2 is null or cnt2<=cnt then '승인' else '취소' end as status
  from
  (
  select case when cnt>0 then count(name)over(PARTITION by name order by key) end cnt2,cnt,name,key
  from (select key,name,
  (select count(*) from a where a.name=b.name) as cnt
  from b)
  )
  order by key;

WITH A AS
(
SELECT 1 KEY, 'C' NAME FROM DUAL
UNION ALL
SELECT 2 KEY, 'A' NAME FROM DUAL
UNION ALL
SELECT 3 KEY, 'B' NAME FROM DUAL
UNION ALL
SELECT 4 KEY, 'A' NAME FROM DUAL
)
,
B AS
(
SELECT 1 KEY, 'C' NAME FROM DUAL
UNION ALL
SELECT 2 KEY, 'A' NAME FROM DUAL
UNION ALL
SELECT 3 KEY, 'B' NAME FROM DUAL
UNION ALL
SELECT 4 KEY, 'A' NAME FROM DUAL
UNION ALL
SELECT 5 KEY, 'A' NAME FROM DUAL
UNION ALL
SELECT 6 KEY, 'B' NAME FROM DUAL
UNION ALL
SELECT 7 KEY, 'C' NAME FROM DUAL
UNION ALL
SELECT 8 KEY, 'D' NAME FROM DUAL
UNION ALL
SELECT 9 KEY, 'E' NAME FROM DUAL
)
SELECT KEY, NAME, CASE WHEN AEX = 'Y' OR RN = 1 THEN '승인' ELSE '취소' END APPYN
  FROM
    ( 
    SELECT B.KEY, B.NAME
          ,ROW_NUMBER() OVER (PARTITION BY B.NAME ORDER BY B.KEY) RN
          ,DECODE(A.KEY,NULL,'N','Y') AEX
      FROM B , A
     WHERE B.KEY = A.KEY(+)
    ORDER BY B.KEY
    )
 ORDER BY APPYN, KEY

우앙 다들 빠르시당ㅋ

with t as
( 
select 1 key , 'C' name from dual union all
select 2 key , 'A' name from dual union all
select 3 key , 'B' name from dual union all
select 4 key , 'A' name from DUAL
) ,
 TT AS(
select 1 key , 'C' name from dual union all
select 2 key , 'A' name from dual union all
select 3 key , 'B' name from dual union all
select 4 key , 'A' name from dual union all
select 5 key , 'A' name from dual union all
select 6 key , 'B' name from dual union all
select 7 key , 'C' name from dual union all
select 8 key , 'D' name from dual union all
select 9 key , 'E' name from dual 
) 
SELECT TT.KEY
        , TT.NAME
        , DENSE_RANK() OVER(ORDER BY T.NAME ASC) A 
        , CASE WHEN T.NAME = TT.NAME THEN '승인' 
               WHEN RANK( ) OVER (PARTITION BY TT.NAME ORDER BY TT.KEY ASC) =1 THEN '승인' ELSE '취소' END 결과   
FROM TT, T
WHERE TT.KEY = T.KEY(+)

 

WITH TA(KEY,NAME) AS(
    SELECT 1, 'C' FROM DUAL UNION ALL
    SELECT 2, 'A' FROM DUAL UNION ALL
    SELECT 3, 'B' FROM DUAL UNION ALL
    SELECT 4, 'A' FROM DUAL
)
, TB(KEY,NAME) AS(
    SELECT 1, 'C' FROM DUAL UNION ALL
    SELECT 2, 'A' FROM DUAL UNION ALL
    SELECT 3, 'B' FROM DUAL UNION ALL
    SELECT 4, 'A' FROM DUAL UNION ALL
    SELECT 5, 'A' FROM DUAL UNION ALL
    SELECT 6, 'B' FROM DUAL UNION ALL
    SELECT 7, 'C' FROM DUAL UNION ALL
    SELECT 8, 'D' FROM DUAL UNION ALL
    SELECT 9, 'E' FROM DUAL
)
SELECT TB.KEY, TB.NAME
     , CASE WHEN TA.CNT >= ROW_NUMBER() OVER(PARTITION BY TB.NAME ORDER BY TB.KEY)
                 OR TA.NAME IS NULL -- A테이블에 없는 NAME인 경우 승인처리
            THEN '승인'
            ELSE '취소'
            END VAL
FROM (
      SELECT NAME, COUNT(*) CNT
      FROM TA
      GROUP BY NAME
      ) TA
      , TB
WHERE TA.NAME(+) = TB.NAME
ORDER BY 3,1
;

jkson님은 저랑 비슷한 생각을 하셨군요..

Select *
, case when rn=1 or a_seq is not null then
'승인'
Else
'취소'
End
From ( select b.*
,count(*) over(partion by b.name order by nullif(b.seq, a.seq) nulls first ) rn
, a.seq as a_seq
From b
, a
Where b.key = a.key( )
)
Order by key
미처 못본 사항이 있어 수정합니다

모두 감사드립니다.

행복한 주말 되세요. ^^