[SQL] 리스트 잘라서 보여주는 쿼리

운영하는 시스템에서 리스트를 뿌려주는 쿼리에 대해 정리하면 좋을 것 같아,
리스트 갯수 별로 자르는 쿼리(예문에서는 15개씩 뿌려줌)를 긁어와 봤다.

jsp

function goPage(page) {
    var frm = document.searchForm;
    frm.action = "/gi/GI001M01.do";
    frm.page.value = page;
    frm.submit();
}

<a href="javascript:goPage( ${i}   );">${i}</a>

DAO.XML

<select id="selectTagCodeList" parameterType="bMap" resultType="bMap">
        <include refid="pagingHeader"/>
        <include refid="selectList-base"/>
        <include refid="pagingFooter"/>
</select>

<sql id="pagingHeader>
    <![CDATA[
            SELECT z.*
                  FROM (
</sql>

<sql id="pagingFooter>
    ) z
    WHERE 1=1
            <if test="pagingEnd != null and pagingEnd != '' ">
                AND z.RN <![CDATA[<=]]> #{pagingEnd}
            </if>
            <if test="pagingEnd != null and pagingEnd != '' ">
                AND z.RN <![CDATA[>]]> #{pagingStart}
            </if>
</sql>

<sql id="selectList-base">
     대충 SELECT 문
</sql>

SQL

SELECT z.*
                FROM (
                
                SELECT ROW_NUMBER() OVER(ORDER BY A.TAG_SCRE_EXPS_NM, A.TAG_ID) AS RN /*행번호*/
                              , COUNT(1) OVER()                                                                                                            AS CNT
                              , A.TAG_ID                                                                                                                              /*태그ID*/
                              , A.TAG_NM                                                                                                                           /*태그명*/
                              , A.TAG_EXPS_NM                                                                                                              /*태그화면노출명*/
                              , A.TRNT_EXPS_YN                                                                                                            /*TN노출여부*/
                              , A.DATA_VL_TP_CLSF_NM                                                                                            /*데이터값유형구분명*/
                 FROM DTJM1021 A     /*태그코드*/
                 WHERE 1=1
                        AND (A.TRNT_EXPS_YN = 'Y'
                                    OR DATA_VL_TP_CLSF_NM = 'SCR') /*TN노출 대상*/

                 ORDER BY A.TAG_SCRE_EXPS_NM, A.TAG_ID
                 
                 )   z
                 WHERE 1=1
                         AND z.rn <= 15

* 안에 감싸고 있는 SELECT 문 설명
    1. ORDER BY ~ : 정렬할 기준이 될 컬럼 설정(ex. 예문에서는 2개 // A.TAG_SCRE_EXPS_NM, A.TAG_ID)
    2. WHERE: 보여줄 조건 절 설정
    3. 행번호, ROW_NUMBER 설정(단 설정시, 1번에 설정한 정렬 기준과 동일하게 설정)
    4. CNT --> 총 갯수 "COUNT(1) OVER()" 로 셋팅
    5. 1~4로 구성된 위의 값을 포괄하는 z 테이블 설정
* 바깥 WHERE 1=1 AND z.RN <= 15  // RN이 15이하 인 녀석들만 나타나게끔 구성