* 가로 * 세로 (10 * 10), 지뢰가 20개인 Matrix 생성 쿼리
WITH TBL
AS
( SELECT LEVEL AS NO
, CEIL(LEVEL / 10) AS R
, MOD(LEVEL - 1, 10) + 1 AS C
, DECODE(SIGN(ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE) - 21), -1, '*') AS MINE
FROM DUAL
CONNECT BY LEVEL <= 10 * 10
)
SELECT MIN(DECODE(T.C, 1, T.MINE)) AS R1
, MIN(DECODE(T.C, 2, T.MINE)) AS R2
, MIN(DECODE(T.C, 3, T.MINE)) AS R3
, MIN(DECODE(T.C, 4, T.MINE)) AS R4
, MIN(DECODE(T.C, 5, T.MINE)) AS R5
, MIN(DECODE(T.C, 6, T.MINE)) AS R6
, MIN(DECODE(T.C, 7, T.MINE)) AS R7
, MIN(DECODE(T.C, 8, T.MINE)) AS R8
, MIN(DECODE(T.C, 9, T.MINE)) AS R9
, MIN(DECODE(T.C, 10, T.MINE)) AS R10
FROM ( SELECT R
, C
, NVL2(MINE, MINE
, (SELECT SUM(DECODE(D.MINE, '*', 1))
FROM TBL D
WHERE (D.R BETWEEN T.R - 1 AND T.R + 1)
AND (D.C BETWEEN T.C - 1 AND T.C + 1))
) AS MINE
FROM TBL T) T
GROUP BY T.R
;
SQL