데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Oracle Q&A 38951 게시물 읽기

No. 38951

로또 쿼리질문 (통계표?)
작성자	그렇구나~	작성일	2011-10-11 14:51ⓒ 2011-10-11 23:07ⓜ	조회수	7,085

안녕하세요.

혼자서 머리 싸매다가 답이 나오지 않아 질문합니다.

로또 당첨이력을 가지고 아래 예시처럼 표를 만들어 보려고 하는데요...

잘 되지 않습니다. 이렇게 표시되는게 한방에 가능할까요??

CREATE TABLE LOTTO

(

L_NO NUMBER (10) NOT NULL,

DAY VARCHAR2 (8) NOT NULL,

CODE1 NUMBER (2) NOT NULL,

CODE2 NUMBER (2) NOT NULL,

CODE3 NUMBER (2) NOT NULL,

CODE4 NUMBER (2) NOT NULL,

CODE5 NUMBER (2) NOT NULL,

CODE6 NUMBER (2) NOT NULL,

);

<원하는 결과값>
L_NO 01 02 03 04 05 06 07 08 09 10 ..... 41 42 43 44 45 


 462 == == 03 == == == == == == == ..... == == == == 45

 461 == == == == == == == == == == ..... == == == == ==


 460 == == == == == == == == == == ..... == == 43 == 45

 459 == == == 04 == 06 == == == 10 ..... == == == == ==

 ...

 ...

 ...

몇시간 삽질해서 비슷하게 만들어본 쿼리...

select lotto_no "NO",

lpad(code1,code1*2,'==')||

lpad(code2,(code2-code1)*2,'==')||

lpad(code3,(code3-code2)*2,'==')||

lpad(code4,(code4-code3)*2,'==')||

lpad(code5,(code5-code4)*2,'==')||

lpad(code6,(code6-code5)*2,'==')||

lpad('==',(45-code6)*2,'==') "Graph"

from lt.lt order by 1 desc;

이 글에 대한 댓글이 총 4건 있습니다.

무시칸 방법입니다.

WITH aaa AS (
SELECT '456' L_NO, 1 CODE1, 2 code2, 4 code3, 10 code4, 15 code5, 20 code6 FROM dual UNION ALL
SELECT '457' L_NO, 5 CODE1, 17 code2, 2 code3, 30 code4, 8 code5, 25 code6 FROM dual
)
SELECT CASE WHEN CODE1 = 1 OR CODE2 = 1 OR CODE3 = 1 OR CODE4 = 1 OR CODE5 = 1 OR CODE6 = 1 THEN '01' ELSE '==' END NO1
       ,CASE WHEN CODE1 = 2 OR CODE2 = 2 OR CODE3 = 2 OR CODE4 = 2 OR CODE5 = 2 OR CODE6 = 2 THEN '02' ELSE '==' END NO2
       ,CASE WHEN CODE1 = 3 OR CODE2 = 3 OR CODE3 = 3 OR CODE4 = 3 OR CODE5 = 3 OR CODE6 = 3 THEN '03' ELSE '==' END NO3
       ,CASE WHEN CODE1 = 4 OR CODE2 = 4 OR CODE3 = 4 OR CODE4 = 4 OR CODE5 = 4 OR CODE6 = 4 THEN '04' ELSE '==' END NO4
       ,CASE WHEN CODE1 = 5 OR CODE2 = 5 OR CODE3 = 5 OR CODE4 = 5 OR CODE5 = 5 OR CODE6 = 5 THEN '05' ELSE '==' END NO5
       ,CASE WHEN CODE1 = 6 OR CODE2 = 6 OR CODE3 = 6 OR CODE4 = 6 OR CODE5 = 6 OR CODE6 = 6 THEN '06' ELSE '==' END NO6
       ,CASE WHEN CODE1 = 7 OR CODE2 = 7 OR CODE3 = 7 OR CODE4 = 7 OR CODE5 = 7 OR CODE6 = 7 THEN '07' ELSE '==' END NO7
       ,CASE WHEN CODE1 = 8 OR CODE2 = 8 OR CODE3 = 8 OR CODE4 = 8 OR CODE5 = 8 OR CODE6 = 8 THEN '08' ELSE '==' END NO8
       ,CASE WHEN CODE1 = 9 OR CODE2 = 9 OR CODE3 = 9 OR CODE4 = 9 OR CODE5 = 9 OR CODE6 = 9 THEN '09' ELSE '==' END NO9
       ,CASE WHEN CODE1 = 10 OR CODE2 = 10 OR CODE3 = 10 OR CODE4 = 10 OR CODE5 = 10 OR CODE6 = 10 THEN '10' ELSE '==' END NO10
       ,CASE WHEN CODE1 = 11 OR CODE2 = 11 OR CODE3 = 11 OR CODE4 = 11 OR CODE5 = 11 OR CODE6 = 11 THEN '11' ELSE '==' END NO11
       ,CASE WHEN CODE1 = 12 OR CODE2 = 12 OR CODE3 = 12 OR CODE4 = 12 OR CODE5 = 12 OR CODE6 = 12 THEN '12' ELSE '==' END NO12
       ,CASE WHEN CODE1 = 13 OR CODE2 = 13 OR CODE3 = 13 OR CODE4 = 13 OR CODE5 = 13 OR CODE6 = 13 THEN '13' ELSE '==' END NO13
       ,CASE WHEN CODE1 = 14 OR CODE2 = 14 OR CODE3 = 14 OR CODE4 = 14 OR CODE5 = 14 OR CODE6 = 14 THEN '14' ELSE '==' END NO14
       ,CASE WHEN CODE1 = 15 OR CODE2 = 15 OR CODE3 = 15 OR CODE4 = 15 OR CODE5 = 15 OR CODE6 = 15 THEN '15' ELSE '==' END NO15
       ,CASE WHEN CODE1 = 16 OR CODE2 = 16 OR CODE3 = 16 OR CODE4 = 16 OR CODE5 = 16 OR CODE6 = 16 THEN '16' ELSE '==' END NO16
       ,CASE WHEN CODE1 = 17 OR CODE2 = 17 OR CODE3 = 17 OR CODE4 = 17 OR CODE5 = 17 OR CODE6 = 17 THEN '17' ELSE '==' END NO17
       ,CASE WHEN CODE1 = 18 OR CODE2 = 18 OR CODE3 = 18 OR CODE4 = 18 OR CODE5 = 18 OR CODE6 = 18 THEN '18' ELSE '==' END NO18
       ,CASE WHEN CODE1 = 19 OR CODE2 = 19 OR CODE3 = 19 OR CODE4 = 19 OR CODE5 = 19 OR CODE6 = 19 THEN '19' ELSE '==' END NO19
       ,CASE WHEN CODE1 = 20 OR CODE2 = 20 OR CODE3 = 20 OR CODE4 = 20 OR CODE5 = 20 OR CODE6 = 20 THEN '20' ELSE '==' END NO20
       ,CASE WHEN CODE1 = 21 OR CODE2 = 21 OR CODE3 = 21 OR CODE4 = 21 OR CODE5 = 21 OR CODE6 = 21 THEN '21' ELSE '==' END NO21
       ,CASE WHEN CODE1 = 22 OR CODE2 = 22 OR CODE3 = 22 OR CODE4 = 22 OR CODE5 = 22 OR CODE6 = 22 THEN '22' ELSE '==' END NO22
       ,CASE WHEN CODE1 = 23 OR CODE2 = 23 OR CODE3 = 23 OR CODE4 = 23 OR CODE5 = 23 OR CODE6 = 23 THEN '23' ELSE '==' END NO23
       ,CASE WHEN CODE1 = 24 OR CODE2 = 24 OR CODE3 = 24 OR CODE4 = 24 OR CODE5 = 24 OR CODE6 = 24 THEN '24' ELSE '==' END NO24
       ,CASE WHEN CODE1 = 25 OR CODE2 = 25 OR CODE3 = 25 OR CODE4 = 25 OR CODE5 = 25 OR CODE6 = 25 THEN '25' ELSE '==' END NO25
       ,CASE WHEN CODE1 = 26 OR CODE2 = 26 OR CODE3 = 26 OR CODE4 = 26 OR CODE5 = 26 OR CODE6 = 26 THEN '26' ELSE '==' END NO26
       ,CASE WHEN CODE1 = 27 OR CODE2 = 27 OR CODE3 = 27 OR CODE4 = 27 OR CODE5 = 27 OR CODE6 = 27 THEN '27' ELSE '==' END NO27
       ,CASE WHEN CODE1 = 28 OR CODE2 = 28 OR CODE3 = 28 OR CODE4 = 28 OR CODE5 = 28 OR CODE6 = 28 THEN '28' ELSE '==' END NO28
       ,CASE WHEN CODE1 = 29 OR CODE2 = 29 OR CODE3 = 29 OR CODE4 = 29 OR CODE5 = 29 OR CODE6 = 29 THEN '29' ELSE '==' END NO29
       ,CASE WHEN CODE1 = 30 OR CODE2 = 30 OR CODE3 = 30 OR CODE4 = 30 OR CODE5 = 30 OR CODE6 = 30 THEN '30' ELSE '==' END NO30
   FROM AAA

DOL님이 2011-10-11 20:05에 작성한 댓글입니다. Edit Ｘ

WITH aaa AS (
SELECT '456' L_NO, 1 CODE1, 2 code2, 4 code3, 10 code4, 15 code5, 45 code6 FROM dual UNION ALL
SELECT '457' L_NO, 5 CODE1, 17 code2, 2 code3, 30 code4, 8 code5, 25 code6 FROM dual
)
select l_no,
        case when instr(code,',1,') > 0 then ' 01 ' else '== ' end||
        case when instr(code,',2,') > 0 then ' 02 ' else '== ' end||
        case when instr(code,',3,') > 0 then ' 03 ' else '== ' end||
        case when instr(code,',4,') > 0 then ' 04 ' else '== ' end||
        case when instr(code,',5,') > 0 then ' 05 ' else '== ' end||
        case when instr(code,',45,') > 0 then ' 45 ' else '== ' end graph
from (
select l_no,
        ','||code1||','||code2||','||code3||','||code4||','||code5||','||code6||',' code
from aaa
)

k님이 2011-10-11 20:31에 작성한 댓글입니다. Edit Ｘ

두분 답변 감사합니다.

글쓴이님이 2011-10-12 17:50에 작성한 댓글입니다. Edit Ｘ

없는칸에 ===가 나와야한다면..

WITH t AS (

SELECT '456' L_NO, 1 CODE1, 2 code2, 4 code3, 10 code4, 15 code5, 20 code6 FROM dual

UNION ALL SELECT '457' L_NO, 5 CODE1, 17 code2, 2 code3, 30 code4, 8 code5, 25 code6 FROM dual

)

select *

from

(

select

tmp.l_no,tmp.lvl,decode(a.data,null,'===',a.data) data

from

(

select l_no,code1,code2,code3,code4,code5,code6 from t

)

unpivot

(

data for value_type in (code1,code2,code3,code4,code5,code6)

)

) a,

(select lvl,tt.l_no from

(select level lvl from dual connect by level <= 46),(select distinct l_no from t) tt

) tmp

where 1=1

and a.l_no(+) = tmp.l_no

and a.data(+) = tmp.lvl

)

pivot

(

max(data)

for lvl in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40

,41,42,43,44,45,46

)

===가 안나와도 된다면.

WITH t AS (

SELECT '456' L_NO, 1 CODE1, 2 code2, 4 code3, 10 code4, 15 code5, 20 code6 FROM dual

UNION ALL SELECT '457' L_NO, 5 CODE1, 17 code2, 2 code3, 30 code4, 8 code5, 25 code6 FROM dual

)

select *

from

(

select

a.l_no,tmp.lvl,a.data

from

(

select l_no,code1,code2,code3,code4,code5,code6 from t

)

unpivot

(

data for value_type in (code1,code2,code3,code4,code5,code6)

)

) a,

(select level lvl from dual connect by level <= 46) tmp

where 1=1

and a.data(+) = tmp.lvl

)

pivot

(

max(data)

for lvl in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40

,41,42,43,44,45,46

)

sdalf님이 2011-10-20 12:41에 작성한 댓글입니다.
이 댓글은 2011-10-20 12:43에 마지막으로 수정되었습니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
38956	쿼리 튜닝좀 부탁드립니다. [1]	박주영	2011-10-12	4714
38953	ORA-24315 illegal attribute type	이동희	2011-10-12	4701
38952	오라클 db문제입니다. 도와주세요. ㅠㅠ [4]	긴급도움요청	2011-10-11	13083
38951	로또 쿼리질문 (통계표?) [4]	그렇구나~	2011-10-11	7085
38950	group by 와 distinct의 차이점?? [1]	궁금	2011-10-11	5261
38949	오라클 10g EM (최상위 작업에서 SQL문 텍스트 풀쿼리 조회 방법) [2]	라클리	2011-10-10	4053
38948	오라클9i에서 특정 문자들(다국어)들이 깨져서 들어갑니다. [1]	hym77	2011-10-10	4935