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')
이런식으로 sql문을 짜고 rownum 써서 20개씩 출력하여 웹에 뿌리고 싶습니다
어떤식으로 해야할지 막혀서 조언부탁드립니다
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
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
이런식으로 변경해보면 되긴하는대 결과가 이상하게 나옵니다
인라인 뷰로 한 번더 묶어야 겠네요.
SELECT * FROM (SELECT ROWNUM NO, 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 정렬컬럼 ASC) A) WHERE NO BETWEEN 1 AND 20;
"페이지" 라는 변수를 이용하여 사용하셔도 될 듯합니다. SELECT A.* FROM ( SELECT ROW_NUMBER()OVER(ORDER BY 정렬컬럼) NUM , 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') ) A WHERE NUM BETWEEN (((페이지 - 1) * 20) + 1) AND (페이지 * 20)