mybatis 동적 컬럼에 따라 JOIN 하기...

dao에서 List<Map<String, Object>>으로 리턴하고

	/**
	 */
	public List<Map<String, Object>> getDailySalesStaticsList(SalesSearchParamVO searchParam) {
		// TODO Auto-generated method stub
		return selectList("selectDailySalesStaticsList", searchParam);
	}

 

mybatis에서 resultType="java.util.LinkedHashMap" 으로 받는다.

	<select id="selectDailyStaticsList" resultType="java.util.LinkedHashMap">
	SELECT *
	<if test="martCodes != null and martCodes != ''">
		,
			<foreach collection="martListCol" item="item" index="index" separator="" open="" close="">
				+IFNULL(SUM(amount_${item}),0)
			</foreach>
		AS rowSum
	</if>
	FROM(SELECT date_format(ymd, '%Y-%m-%d') AS date_dd 
			,DAYOFWEEK(ymd) AS date_wk
    		FROM date_table
			WHERE ymd between #{startDate} and #{endDate}
			) H
			<if test="martCodes != null and martCodes != ''">
					<foreach collection="martListCol" item="item" index="index" separator="" open="" close="">
						LEFT JOIN
						(SELECT ord.ord_mart AS ord_mart_${item}
								,mart.mart_name AS mart_name_${item}
						
						        ,date(ord.ord_date) AS date_dd_${item}
						
						 		,SUM(ordd.ordd_price) AS amount_${item}
	
								FROM ord
								LEFT JOIN mart ON ord.ord_mart = mart.mart_code
 								LEFT JOIN ordd ON ord.ord_code = ordd.ordd_code  AND ordd.ordd_mart = ${item}
 								LEFT JOIN goods ON ordd.ordd_goods = goods.goods_code AND goods.goods_mart = ${item}
								WHERE ord.ord_regtype <![CDATA[<>]]> 3 
								AND ord_date between CONCAT(#{startDate},' 00:00:00') and CONCAT(#{endDate},' 23:59:59')
								AND ord.ord_mart =${item}
								<if test="topBook > 0">
									AND goods.goods_bktop = #{topBook}
								</if>
								<if test="midBook > 0">
									AND goods.goods_bkmid = #{midBook}
								</if>
								<if test="botBook > 0">
									AND goods.goods_bkbot = #{botBook}
								</if>
								GROUP BY DATE(ord_date)
						) `${item}`
						ON H.date_dd = `${item}`.date_dd_${item}
					</foreach>
					
					GROUP BY H.date_dd
					ORDER BY H.date_dd DESC
			</if>			
	</select>

 

※ 주의 사항: mysql 또는 mariadb에서는 join을 최대 61개 까지 밖에 안 된다.

join을 61개 이상 해야 하는 경우는 다른 방식을 고민 해봐야 한다...