퀴즈란에 올리시고 정답도 좀 ㅋㅋㅋ

-- 샘플 데이터는 대충
WITH T AS (
SELECT X , Y , CASE WHEN MOD(Y,3) = 1 OR X <= 3  THEN 'O' ELSE NULL END  VAL
  FROM (SELECT LEVEL AS  X  FROM DUAL CONNECT BY LEVEL <= 10 ) A
     , (SELECT LEVEL AS  Y  FROM DUAL CONNECT BY LEVEL <= 10 ) B
) , T_ARR AS (
SELECT * 
  FROM T
 PIVOT ( MIN(VAL) FOR Y IN(1 V1, 2 V2, 3 V3, 4 V4, 5 V5, 6 V6, 7 V7, 8 V8, 9 V9, 10 V10))
)
SELECT * 
 FROM (SELECT X , Y , VAL
            , ROW_NUMBER() OVER(ORDER BY X , Y ) RN
         FROM T_ARR
      UNPIVOT INCLUDE NULLS ( VAL FOR Y IN (V1 AS 1, V2 AS 2 ,V3 AS 3, V4 AS 4, V5 AS 5, V6 AS 6 ,V7 AS 7, V8 AS 8 , V9 AS 9, V10 AS 10  ) )
        WHERE VAL IS NULL ) 
 WHERE RN = 1

물어보는 입장인데 정답은.. ㅎㅎ

샘플 데이터가 좀 걸리는데, 레코드 자체가 일부만 있는 상황이라서 말이죠.

모든 좌표를 다 채워서 타겟으로 만들어둘수가 없지 않을까 싶어요.

SELECT X , Y
      , s.pos_x  VAL
  FROM (SELECT LEVEL AS  X  FROM DUAL CONNECT BY LEVEL <= 10 ) A
     , (SELECT LEVEL AS  Y  FROM DUAL CONNECT BY LEVEL <= 10 ) B
     , T_TARGET S
  where s.pos_y(+) = b.Y
    and s.pos_x(+) = a.x
;

이렇게 해야 올려주신 샘플 데이터랑 같은 형태가 될텐데.. 이건 이중 포괄조인으로 걸려서..

with smp as (
         Select 1 X, 2 Y from Dual
  union Select 3 X, 2 Y From Dual
  union Select 1 X, 3 Y From Dual
  union Select 2 X, 3 Y From Dual
  )
  Select * from smp

오~~ 지금 제가 헤매고 있는 형태보다 훨씬 깔끔하네요.

레벨 X,Y를 한 테이블로 넣고 Left Outer Join 으로 해결하셨군요.

Order by 만 Y, X 로 바꾸면 바로 제가 원하던 답이네요.

SELECT  X, Y
  FROM (SELECT X , Y , VAL
          FROM (SELECT T1.X ,T1.Y, T2.Pos_X AS val
							    FROM (SELECT X , Y
									   		  FROM (SELECT LEVEL AS  X  FROM DUAL CONNECT BY LEVEL <= 3 ) A
											     , (SELECT LEVEL AS  Y  FROM DUAL CONNECT BY LEVEL <= 3 ) B
											  ) T1
							    LEFT OUTER JOIN T_TARGET T2
							      ON   T1.X = T2.Pos_X
							       AND T1.Y = T2.Pos_Y
                ) T_ARR
         WHERE VAL IS NULL
         ORDER BY Y , X
        )
  WHERE ROWNUM <= 1
;

-- 다 합체 하니 이렇게 되었네요. 
-- 고맙습니다.

-- 레벨X, Y를 한 쿼리에 넣는것도 해봤는데 그건 왜 안됐었는지 기억이 안나넹..ㅋ

Select X, Y from (
  Select Y, X, Val, Rank() Over( order by y, x) RK
    FROM (select t.y, t.x, a.Pos_x as Val
						from T_TARGET a
						   , (select y, x
						      from (Select level y from dual connect by level <= 3) a
						         , (Select level x from dual connect by level <= 3) b
						     ) t
						where 1=1
						  and t.y = a.pos_y(+)
						  and t.x = a.pos_x(+)			
		  ) T_ARR
   Where Val is Null
 )
 Where RK = 1
  ;

Rank를 써봐도 별 차이는 없네요.

SELECT /*+ LEADING(a) USE_NL(b) */
       a.x, a.y
  FROM (SELECT CEIL(LEVEL / 10) y
             , MOD(LEVEL - 1, 10) + 1 x
          FROM dual CONNECT BY LEVEL <= 100
        ) a
     , smp b
 WHERE a.y = b.y(+)
   AND a.x = b.x(+)
   AND b.x IS NULL
   AND ROWNUM = 1
;