나만의생각
338
2017-06-27 10:57:01 작성 2017-06-27 11:32:16 수정됨
3
872

쿼리 rownum 질문



SELECT *

  FROM (

    SELECT ROWNUM AS RNUM,

    SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼1 ,

    SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼2 ,

    SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼3 ,

    SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼4 ,

    SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼5 ,

    TO_CHAR(시간, 'YYYY-MM-DD') as 입력날짜

    FROM 테이블

    GROUP BY TO_CHAR(DTIME, 'YYYY-MM-DD')

    order by TO_CHAR(DTIME, 'YYYY-MM-DD') 

)

WHERE RNUM BETWEEN 1 AND 20

이런식으로 sql문을 썻는대 범위가 20~ 40으로 변경시 결과가 안나옵니다 

조언 부탁드립니다

0
  • 답변 3

  • 나만의생각
    338
    2017-06-27 11:52:27 작성 2017-06-27 11:57:19 수정됨


    SELECT *

      FROM (

        SELECT ROWNUM AS RNUM,

        SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼1 ,

        SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼2 ,

        SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼3 ,

        SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼4 ,

        SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼5 ,

        TO_CHAR(시간, 'YYYY-MM-DD') as 입력날짜,

         rownum as rum

        FROM 테이블

        GROUP BY rownum ,TO_CHAR(DTIME, 'YYYY-MM-DD')

        order by TO_CHAR(DTIME, 'YYYY-MM-DD') 

    )

    WHERE RNUM BETWEEN 20 AND 40

     

    이런식으로 변경해보면 되긴하는대 결과가 이상하게 나옵니다


    날자가 10.26~12.20 까지 나와야하는대 

    11.25일로 고정됩니다


  • Dev03
    166
    2017-06-27 13:39:18

    SELECT *

      FROM (

    SELECT ROWNUM AS RNUM, A.*

     FROM (

     

       SELECT  SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼1 ,

           SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼2 ,

       SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼3 ,

       SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼4 ,

       SUM( DECODE(컬럼명,'1' ,1,0)) AS 컬럼5 ,

       TO_CHAR(시간, 'YYYY-MM-DD') as 입력날짜

        FROM 테이블

       GROUP BY TO_CHAR(DTIME, 'YYYY-MM-DD')

       order by TO_CHAR(DTIME, 'YYYY-MM-DD') 

    ) A

    WHERE ROWNUM <= 40

    ) B 

    WHERE RNUM BETWEEN 20 AND 40

  • 나만의생각
    338
    2017-06-27 14:59:30

    Dev03/감사합니다 

  • 로그인을 하시면 답변 을 등록할 수 있습니다.