로또번호생성

어쩌다 한번하는데, 자동 로또가 잘 안된다....

다음번에 간만에 SQL로 추출해서 적어 봐야겠다...

빼고 싶은 번호를 넣어 봤다. 빈번호는 그날 쓰고 싶은 걸로 쓰자.

전주 당첨번호는 모두 뺀다거나..에혀 인생..로또를 꿈꿔야 하다니...생각보다 불쌍타....

 

SELECT
     GAME
    ,MIN(DECODE(RNUM,RS,NUM)) NUM1
    ,MIN(DECODE(RNUM,RS+1,NUM)) NUM2
    ,MIN(DECODE(RNUM,RS+2,NUM)) NUM3
    ,MIN(DECODE(RNUM,RS+3,NUM)) NUM4
    ,MIN(DECODE(RNUM,RS+4,NUM)) NUM5
    ,MIN(DECODE(RNUM,RS+5,NUM)) NUM6
FROM
(
    SELECT
         A.GAME
        ,A.NUM
        ,A.RNUM
        ,B.RS
    FROM
    (
        SELECT
             GAME
            ,NUM
            --,DECODE(MOD(ROWNUM,45),0,45,MOD(ROWNUM,45)) AS RNUM
            ,MOD(ROWNUM-1,45)+1 AS RNUM
        FROM
        (
            SELECT CEIL(LEVEL/45) GAME, MOD(LEVEL,45)+1 NUM FROM DUAL CONNECT BY LEVEL <= 45*5 ORDER BY CEIL(LEVEL/45),DBMS_RANDOM.VALUE()
        ) WHERE NUM NOT IN (3,10,16,19,31,39)
    ) A , ( SELECT TRUNC(DBMS_RANDOM.VALUE(1,41)) RS FROM DUAL WHERE ROWNUM=1 ) B 
) WHERE RNUM BETWEEN RS AND RS+5
GROUP BY GAME ORDER BY GAME;
SELECT
     GAME
    ,MIN(DECODE(RNUM,1,NUM)) NUM1
    ,MIN(DECODE(RNUM,2,NUM)) NUM2
    ,MIN(DECODE(RNUM,3,NUM)) NUM3
    ,MIN(DECODE(RNUM,4,NUM)) NUM4
    ,MIN(DECODE(RNUM,5,NUM)) NUM5
    ,MIN(DECODE(RNUM,6,NUM)) NUM6
FROM
(
    SELECT
         C.GAME
        ,C.NUM
        ,ROW_NUMBER() OVER(PARTITION BY GAME ORDER BY NUM) RNUM
    FROM
    (
        SELECT
             A.GAME
            ,A.NUM
            ,A.RNUM
            ,B.RS
        FROM
        (
            SELECT
                 GAME
                ,NUM
                ,MOD(ROWNUM-1,45)+1 AS RNUM
            FROM
            (
                SELECT CEIL(LEVEL/45) GAME, MOD(LEVEL,45)+1 NUM FROM DUAL CONNECT BY LEVEL <= 45*5 ORDER BY CEIL(LEVEL/45),DBMS_RANDOM.VALUE()
            ) WHERE NUM NOT IN (3,10,16,19,31,39)
        ) A , ( SELECT TRUNC(DBMS_RANDOM.VALUE(1,41)) RS FROM DUAL WHERE ROWNUM=1 ) B
    ) C WHERE RNUM BETWEEN RS AND RS+5
) D GROUP BY GAME ORDER BY GAME