Oracle LISTAGG 사용 예시 / [직급]이름(시간) 테이블 4개 참조 예시

 

<!-- 추가근무  selectList -->
<select id="selectList" parameterType="VO" resultType="VO">
    /* VO.selectList */
    <![CDATA[ 
    SELECT  TT.*
       FROM   (  
             SELECT  ROWNUM AS RNUM, OT.* 
               FROM  (
                   SELECT A.OT_SEQ                                                                       		  otSeq
                        , TO_CHAR(A.RGST_DT,'YYYY.MM.DD')                                                		  rgstDt
                        , C.NAME                                                                                  rgstId   
                        , (SELECT TA.NAME FROM t_ad_user TA WHERE TA.USE_YN = 'Y' AND TA.SEQ = A.APPROVER )       approver
                        , A.STATUS                                                              				  status   
                        , A.PRJCT_NM                                                               			      prjctNm     
                        , A.APPLY_RSN                                                          				      applyRsn
                        , TO_CHAR(A.APPLY_DATE, 'YYYY.MM.DD')                                          		  	  applyDate   
                        , A.ATCH_FILE_ID                                                            			  atchFileId
                        , 'OPEN-W-'||LPAD(A.OT_SEQ,5,'0')                                             			  docuNum
                        , B.emplyName        																	  emplyName
                     FROM  T_OVER_TIME A
          LEFT OUTER JOIN (SELECT
                                  A.OT_SEQ   
                                  , LISTAGG('[' || CD.C_CODE_NM ||  ']' || B.NAME||'('||CASE WHEN A.TOTAL_HOUR IS NOT NULL AND A.TOTAL_HOUR <> '0' THEN A.TOTAL_HOUR||'시간' ELSE '' END
                                  ||CASE WHEN A.TOTAL_MIN IS NOT NULL AND A.TOTAL_MIN <> '0' THEN A.TOTAL_MIN||'분' ELSE '' END||')',', ') WITHIN GROUP(ORDER BY A.AD_SEQ)  emplyName
                               FROM T_OVER_ADD A
                    LEFT OUTER JOIN T_AD_USER B
                                 ON B.SEQ = REPLACE(REPLACE(A.EMPLY_SEQ, '[', ''), ']', '')
                                AND B.USE_YN = 'Y'
                    LEFT OUTER JOIN T_CODE CD
                                 ON CD.C_CODE = B.POSITION             
                              WHERE A.USE_YN = 'Y' 
                              GROUP BY A.OT_SEQ
                           ) B
                            ON  A.OT_SEQ = B.OT_SEQ
               LEFT OUTER JOIN  T_AD_USER C 
                            ON  A.RGST_ID = C.SEQ 
                           AND  C.USE_YN = 'Y'
                         WHERE  A.USE_YN='Y'
    ]]>      
        <include refid="Where"/>        
    <![CDATA[  
                         ORDER 	BY A.OT_SEQ DESC 
                      ) OT   
                WHERE  ROWNUM <= #{firstIndex} + #{recordCountPerPage}
               ) TT
        WHERE TT.RNUM > #{firstIndex}
     ]]>  
</select>

1. 회원 테이블

2. 코드 테이블 (직급 정보)

3. 초과근무 테이블 

4. 초과근무 상세 인원 및 작업내용 테이블