Oracle LISTAGG 실전 사용 예시 / MySQL GROUP_CONCAT / LEFT OUTER JOIN

<select id="excelDownSelectList" parameterType="vo" resultType="vo">
		/* vo.excelDownSelectList*/
		<![CDATA[ 
			SELECT 				ROWNUM														   						  excelRow
                        	  ,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(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 A.EMPLY_SEQ = B.SEQ
                                     AND B.USE_YN = 'Y'
                                   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.RGST_DT ASC, A.OT_SEQ ASC  
		]]>	
	</select>

1. LEFT OUTER JOIN 서브쿼리 활용

2. LISTAGG 를 활용하여 명단을 조회