볼링 게임 스코어 생성
-- n: 쓰러지는 핀의 최소 수
with t as (
select 7 f from dual)
select frm, ord
, decode(ord,1,decode(scr1,10,'X',scr1)
,2,case when frm=10 and spr=20 then 'X'
when scr1>0 and spr=10 then '/'
else scr1||'' end
,3,decode(sum(decode(ord,2,scr1,3,scr2)) over (partition by frm),20,'X',10,'/',scr2||'')
) pnt
--, scr, scrx, scr1, spr
from
(
select frm, ord, scr, scrx, scr1
, case when ord=3 and spr=20 then scrx else trunc(dbms_random.value(0,20-spr+1)) end scr2
, spr
from
(
select frm, ord, scr, scrx, scr1, sum(decode(ord,1,scr1,2,scr1)) over (partition by frm) spr
from
(
select frm, ord, scr, scrx
, decode(ord,1,scr
,3,scr
,2,case when frm=10 and scr=10 then scrx else trunc(dbms_random.value(0,10-scr+1)) end
) scr1
from
(
select level frm
, 1+trunc(dbms_random.value(f-1,10)) scr
, 1+trunc(dbms_random.value(f-1,10)) scrx
from t
connect by level<=10
) a,
(
select level ord
from t
connect by level<=3
) b
) a1
) a2
) a3
where ord=1 or (ord=2 and (frm=10 or scr<>10)) or (ord=3 and frm=10 and scr=10)
order by frm, ord
결과
FRM ORD PNT
---------- ---------- ----------------------------------------
1 1 X
2 1 9
2 2 0
3 1 7
3 2 /
4 1 7
4 2 2
5 1 X
6 1 X
7 1 9
7 2 0
8 1 8
8 2 1
9 1 9
9 2 /
10 1 7
10 2 /