데이터베이스 퀴즈 및 튜닝 문제
[퀴즈] 가위 바위 보에서 서로 비기는 경우 구하기
[문제]
가위 바위 보 게임의 참가자 테이블 T1 과 코드테이블 T2 가 있을 때
다음 결과를 도출하는 SQL 을 작성하세요.
가위 바위 보 게임 참가자 3명이 게임을 했을 때
조합 가능한 모든 경우 중 비기는 경우를 구하는 문제입니다.
코드테이블은 변동 없습니다. 참가자는 늘어날 수 있습니다.
참가자가 늘어나는 경우까지 고려하여 SQL 을 작성해 주세요.
[샘플]
WITH t1 AS
(
SELECT 1 id, 'AA' nm FROM dual
UNION ALL SELECT 2, 'BB' FROM dual
UNION ALL SELECT 3, 'CC' FROM dual
--UNION ALL SELECT 4, 'DD' FROM dual
)
, t2 AS
(
SELECT 1 cd, '가위' v FROM dual
UNION ALL SELECT 2, '바위' FROM dual
UNION ALL SELECT 3, '보' FROM dual
)
SELECT * FROM t1, t2;
[원본]
게임참가자 테이블
코드 테이블
ID
NM
CD
V
1
AA
1
가위
2
BB
2
바위
3
CC
3
보
[결과]
P
AA가위-BB가위-CC가위
AA가위-BB바위-CC보
AA가위-BB보-CC바위
AA바위-BB가위-CC보
AA바위-BB바위-CC바위
AA바위-BB보-CC가위
AA보-BB가위-CC바위
AA보-BB바위-CC가위
AA보-BB보-CC보
[정답] 아래부분 마우스 드래그하세요SELECT p
FROM (SELECT SUBSTR(SYS_CONNECT_BY_PATH(nm||v, '-'), 2) p
FROM t1, t2
WHERE CONNECT_BY_ISLEAF = 1
START WITH id = 1
CONNECT BY PRIOR id + 1 = id
) t3
, t2
WHERE INSTR(p, v) > 0
GROUP BY p
HAVING COUNT(*) IN (1, 3)
ORDER BY p
;
세 사람이 가위바위보를 하면.
가위=1, 바위=2, 보=3이라고 했을 때
아래처럼 경우가 나올 수 있는거 같은데(27 = 3 * 3 * 3)
이 조합에서 비긴 사람만 본다면,
3사람이 다 비겨야 비겼다고 볼 수 있나요?
2사람만 같아도 비긴 결과가 있다고 볼 수 있나요?
결과셋을 보면 더 헤갈리고.. ㅡ ㅅ ㅡ)/
비겼다고 하는게 같은 것을 낸 사람 아닌가용?
1.1.1
1.1.2
1.1.3
1.2.1
1.2.2
1.2.3
1.3.1
1.3.2
1.3.3
2.1.1
2.1.2
2.1.3
2.2.1
2.2.2
2.2.3
2.3.1
2.3.2
2.3.3
3.1.1
3.1.2
3.1.3
3.2.1
3.2.2
3.2.3
3.3.1
3.3.2
3.3.3
실제로 가위바위보할때도 그렇잖아요 ㅎㅎ
1.1.1 --비김
1.1.2
1.1.3
1.2.1
1.2.2
1.2.3 --비김
1.3.1
1.3.2 --비김
1.3.3
2.1.1
2.1.2
2.1.3
2.2.1
2.2.2 --비김
2.2.3
2.3.1
2.3.2
2.3.3
3.1.1
3.1.2
3.1.3
3.2.1
3.2.2
3.2.3
3.3.1
3.3.2
3.3.3 ---비김
잘못생각했어요
1번 가위 2번 가위 3번 보 일때는 비긴게아닌게되니
1,2,3 이 모두 다를경우도 비긴상태
아하..... ㅡ ㅅ ㅡ) 감사합니다.
이 닭대가뤼...
-- 넘어려워요 상주셔야 돼요 ㅎㅎ
SELECT 가위바위보
FROM (
SELECT 가위바위보
, DECODE ( LENGTH ( 가위바위보 ), LENGTH ( REPLACE ( 가위바위보, '가위', '' ) ), 'X', 'O' ) 가위
, DECODE ( LENGTH ( 가위바위보 ), LENGTH ( REPLACE ( 가위바위보, '바위', '' ) ), 'X', 'O' ) 바위
, DECODE ( LENGTH ( 가위바위보 ), LENGTH ( REPLACE ( 가위바위보, '보' , '' ) ), 'X', 'O' ) 보
FROM (
SELECT SUBSTR ( SYS_CONNECT_BY_PATH ( a.nm || b.v, '-' ), 2 ) 가위바위보
, level lv
, cnt
FROM ( SELECT t1.*
, count(*) over() cnt
FROM t1
) a
, t2 b
START WITH id = 1
CONNECT BY PRIOR a.id >= level
AND PRIOR a.id < a.id
) WHERE lv = 3
)
WHERE NVL ( DECODE ( REGEXP_COUNT ( 가위||바위||보, '[O]' ), 3, '비김' )
, DECODE ( REGEXP_COUNT ( 가위||바위||보, '[O]' ), 1, '비김' )
) = '비김'
-- 조금 수정 SELECT 가위바위보 FROM ( SELECT 가위바위보 , CASE WHEN REGEXP_COUNT ( 가위바위보, '가' ) >= 1 THEN 'O' ELSE 'X' END 가위 , CASE WHEN REGEXP_COUNT ( 가위바위보, '바' ) >= 1 THEN 'O' ELSE 'X' END 바위 , CASE WHEN REGEXP_COUNT ( 가위바위보, '보' ) >= 1 THEN 'O' ELSE 'X' END 보 FROM ( SELECT SUBSTR ( SYS_CONNECT_BY_PATH ( a.nm || b.v, '-' ), 2 ) 가위바위보 , level lv , cnt FROM ( SELECT t1.* , count(*) over() cnt FROM t1 ) a , t2 b START WITH id = 1 CONNECT BY PRIOR a.id < a.id ) WHERE lv = cnt ) WHERE DECODE ( REGEXP_COUNT ( 가위||바위||보, '[O]' ), 3, '비김', 1, '비김' ) = '비김'
select result
전 39건이 나오는군요
가위 가위 가위 가위
바위 바위 바위 바위
보 보 보 보
외의 경우는 없을듯한데요?
가위 바위 보 보
가위 바위 보 바위
가위 보 바위 가위
가위 보 바위 보
가위 보 바위 바위
바위 가위 보 가위
바위 가위 보 보
바위 가위 보 바위
보 가위 바위 가위
보 가위 바위 보
보 가위 바위 바위
등등.....
이것도 비기는거에 속하지 않을까요?
SELECT p
가위,바위,보가 한번씩만나와도 비기는거네요.
-- 수정본..
와우... 답글이 많이 달렸네요..
퀴즈에 관심 가져주셔서 감사합니다.
위에 정답 올렸습니다. 확인해 보세요.
정답 설명
이번 퀴즈에서 원하는 답은 다음과 같습니다.
[모든 경우의 수 구하기]
1. t1, t2 를 Cross Join 하고
2. Start With id = 1 Connect by Prior id+1 = id 를 이용한 계층 구조 전개
3. Connect_by_isLeaf 를 이용한 조건절 주기
4. Sys_Connect_by_Path 를 이용한 정보 조회
[비기는 경우 구하기]
5. Instr 을 이용한 조인
6. Count(*) In (1, 3) 을 이용한 조건 처리
답글을 살펴보니 1,2,4,6 까지 생각하신 분은 몇분 계시네요.
안타깝게도 3번과 5번을 생각하신 분은 없네요.
대신 별도 인원카운트를 구하거나, 정규식등을 이용해 주셨네요.
from (
select substr(path1,2) path1
, substr(path2,2) path2
from (
select level lvl
, sys_connect_by_path(data, '-') path1
, sys_connect_by_path(cd, '-') path2
from (
select nm||v data
, id
, cd
from t1
, t2
)
connect by prior id < id and level <= (select count(*) from t1)
)
where lvl = (select count(*) from t1)
)
where (regexp_count(path2, '1') <> 0 and regexp_count(path2, '2') <> 0 and regexp_count(path2, '3') <> 0)
or regexp_count(path2, '1') >= (select count(*) from t1)
or regexp_count(path2, '2') >= (select count(*) from t1)
or regexp_count(path2, '3') >= (select count(*) from t1)
order by path1
아~~
저는 너무 복잡하게 풀었군요.
그래도 답은 맞았습니다. ;;
내일 봐야지.. 오늘 엄청 바뿌넹..
SELECT P
FROM (
SELECT SUBSTR(SYS_CONNECT_BY_PATH(nm||v, '-'), 2) p
,DECODE(INSTR(SYS_CONNECT_BY_PATH(V, '-'),'-가위'),0,0,1) A
,DECODE(INSTR(SYS_CONNECT_BY_PATH(V, '-'),'-바위'),0,0,1) B
,DECODE(INSTR(SYS_CONNECT_BY_PATH(V, '-'),'-보'),0,0,1) C
FROM t1, t2
WHERE CONNECT_BY_ISLEAF = 1
START WITH id = 1
CONNECT BY PRIOR id + 1 = id
)
WHERE A+B+C != 2
SELECT PATH
FROM (
SELECT LEVEL LV, NM, V, SYS_CONNECT_BY_PATH(NM||V,'-') AS PATH
,CONNECT_BY_ISLEAF AS CHK
FROM (
SELECT *
FROM T1,T2
)
START WITH ID = 1
CONNECT BY ID = PRIOR ID + 1
)
WHERE CHK = 1
AND ( (INSTR(PATH,'가위') > 0 AND INSTR(PATH,'바위') > 0 AND INSTR(PATH,'보') > 0)
OR (INSTR(PATH,'가위') > 0 AND INSTR(PATH,'바위') <= 0 AND INSTR(PATH,'보') <= 0)
OR (INSTR(PATH,'가위') <= 0 AND INSTR(PATH,'바위') > 0 AND INSTR(PATH,'보') <= 0)
OR (INSTR(PATH,'가위') <= 0 AND INSTR(PATH,'바위') <= 0 AND INSTR(PATH,'보') > 0) )
;
안보고 풀어봤는데 이게 바로 실력차이구나라는걸 느끼게 해주네요.
많이 배우고 갑니다.
아직 정답은 안보고 작성해봤네요..
사람이 추가된다면?? -> 동적쿼리로..변경하면-_-;;;
WITH t1 AS
(
SELECT 1 id, 'AA' nm
UNION ALL SELECT 2, 'BB'
UNION ALL SELECT 3, 'CC'
--UNION ALL SELECT 4, 'DD'
)
, t2 AS
(
SELECT 1 cd, '가위' v
UNION ALL SELECT 2, '바위'
UNION ALL SELECT 3, '보'
)
select *
from
(SELECT * FROM t1 full outer join t2 on 1 =1 where t1.id = 1
) a
cross apply (SELECT * FROM t1 full outer join t2 on 1 =1 where t1.id = 2) b
cross apply (SELECT * FROM t1 full outer join t2 on 1 =1 where t1.id = 3) c
where (
( a.v = b.v and b.v = c.v )
Or ( a.v <> b.v and b.v <> c.v and c.v <> a.v )
)
우와 정말 쿼리문이 너무 아름다워요... 어떻게 이렇게 간단한 SQL문이 완벽한 답을 출력해 내는지.... 대단하십니다!!
) 버튼을 클릭하여 작성 하시면 됩니다.