프로젝트] Mybatis Sql

<select id="reservationHistory" resultType="java.util.LinkedHashMap" resultMap="ticketing_reservationHistoryResultMap">
   SELECT
      tr.gd_seq AS gdSeq
      ,tr.rs_seq AS rsSeq
      ,tr.rs_status AS rsStatus
      ,tr.sd_seq AS sdSeq
      ,tr.rs_date AS rsDate
      ,tr.rs_buyer_email AS rsBuyerEmail
      ,tr.rs_sd_date AS rsSdDate
      ,tr.rs_buyer_name AS rsBuyerName
      ,tr.rs_buyer_tel AS rsBuyerTel
      ,tr.rs_person AS rsPerson
      ,FUN_GETCOMCODE('RS_STATUS', tr.rs_status) AS rsStatusNm
      ,tg.gd_name AS gdName
      ,DATE_FORMAT(tr.rs_date,'%Y-%m-%d %p %H:%i') AS rsDate
      ,(SELECT count(*) FROM t_memo WHERE rs_seq = tr.rs_seq) AS memoCount
      ,FUN_GETCOMCODE('PAY_METHOD', tp.pm_pay_method) AS pmPayMethodNm
      ,detail.rd_seq AS rdSeq
      ,detail.rd_persons AS rdPerson
      ,detail.rd_price AS rdPrice
      ,detail.rd_amount AS rdAmount
      ,detail.pl_name AS plName
      ,detail.rdp_seq AS rdpSeq
      ,concat(detail.rs_seq, '-', detail.rd_seq, '-', detail.rdp_seq) as inspectNo
   FROM t_reserve tr
      INNER JOIN t_goods tg
         ON tr.idkey = tg.idkey
         AND tr.gd_seq = tg.gd_seq
      INNER JOIN t_payment tp
         ON tr.rs_seq = tp.rs_seq
         AND tp.pm_payment_type = 'PAY'
      LEFT OUTER JOIN t_schedule ts
         ON tr.sd_seq = ts.sd_seq
      INNER JOIN (
         SELECT
            innerTr.parent_rs_seq
            ,innerTr.rs_seq
            ,trd.rd_seq
            ,trd.rd_persons
            ,trd.rd_price
            ,trd.rd_persons * trd.rd_price AS rd_amount
            ,tpl.pl_name
            ,trdp.rdp_seq
         FROM
            t_reserve innerTr, t_reserve_detail trd, t_price_level tpl, t_reserve_detail_person trdp
         WHERE innerTr.gd_package_yn = 'N'
            AND innerTr.rs_seq = trd.rs_seq
            AND trd.pl_seq = tpl.pl_seq
            AND trdp.rd_seq = trd.rd_seq
      ) detail
         ON detail.parent_rs_seq = tr.rs_seq
      WHERE tr.idkey = #{idkey}
         AND (tr.gd_package_yn = 'Y' OR (tr.rs_package_yn = 'N' AND tr.gd_package_yn = 'N'))
         AND tr.rs_reserve_channel NOT IN ('POS','KIOSK')
   <!-- 상품 선택 조건 -->
   <if test='gdSeq != null and gdSeq != ""'>
      AND tr.gd_seq = #{gdSeq}
   </if>
   <if test='rsStatus != null and rsStatus.size != 0'>
      AND (tr.rs_status IN
      <foreach collection="rsStatus" item="item" index="index" separator="," open="(" close=")">
         #{item}
      </foreach>
      OR tr.rs_inspect_yn = #{rsInspectYn})
   </if>
   <!-- 기타 검색 조건 -->
   <if test='searchData != null and searchData != ""'>
      <if test='searchType != null'>
         <if test='searchType == "RSNO"'>
            AND tr.rs_seq = #{searchData}
         </if>
         <if test='searchType == "NAME"'>
            AND tr.rs_buyer_name LIKE CONCAT('%', #{searchData}, '%')
         </if>
         <if test='searchType == "TEL"'>
            AND tr.rs_buyer_tel LIKE CONCAT('%', #{searchData}, '%')
         </if>
      </if>
   </if>
   <if test='searchToDate != null and searchToDate != ""'>
      <if test='searchDateType != null'>
         <!-- 예매일자 검색 조건 -->
         <if test='searchDateType == "BOOKED"'>
            AND tr.rs_date BETWEEN DATE_FORMAT(#{searchToDate},'%Y-%m-%d %p %H:%i:%s')
            AND DATE_FORMAT(#{searchToDate},'%Y-%m-%d %H:%i:%s')
         </if>
         <!-- 입장일자 검색 조건 -->
         <if test='searchDateType == "ENTRY"'>
            AND tr.rs_sd_date = #{searchToDate}
         </if>
         <if test='searchDateType == "ALL"'>
            <![CDATA[
            AND    tr.rs_date >= STR_TO_DATE(tg.gd_sale_startdt, '%Y%m%d')
            ]]>
         </if>
      </if>
   </if>
</select>

 

이런식의 쿼리가 있습니다.

 

그 중에서 눈여겨 볼 것은..

 

,detail.rd_seq AS rdSeq
   ,detail.rd_persons AS rdPerson
   ,detail.rd_price AS rdPrice
   ,detail.rd_amount AS rdAmount <-- 이 부분2
   ,detail.pl_name AS plName
   ,detail.rdp_seq AS rdpSeq
   ,concat(detail.rs_seq, '-', detail.rd_seq, '-', detail.rdp_seq) as inspectNo
FROM t_reserve tr
   INNER JOIN t_goods tg
      ON tr.idkey = tg.idkey
      AND tr.gd_seq = tg.gd_seq
   INNER JOIN t_payment tp
      ON tr.rs_seq = tp.rs_seq
      AND tp.pm_payment_type = 'PAY'
   LEFT OUTER JOIN t_schedule ts
      ON tr.sd_seq = ts.sd_seq
   INNER JOIN (
      SELECT
         innerTr.parent_rs_seq
         ,innerTr.rs_seq
         ,trd.rd_seq
         ,trd.rd_persons
         ,trd.rd_price
         ,trd.rd_persons * trd.rd_price AS rdAmount <-- 이 부분1 을 rs_amount 로 바꿔줘야함.
         ,tpl.pl_name
         ,trdp.rdp_seq
      FROM
         t_reserve innerTr, t_reserve_detail trd, t_price_level tpl, t_reserve_detail_person trdp
      WHERE innerTr.gd_package_yn = 'N'
         AND innerTr.rs_seq = trd.rs_seq
         AND trd.pl_seq = tpl.pl_seq
         AND trdp.rd_seq = trd.rd_seq
   ) detail
      ON detail.parent_rs_seq = tr.rs_seq

 

inner join을 한 상태에서 안에 select 를 한 경우

그리고 컬럼을 바깥에서 사용해야 할 경우에는 AS 를 통해서 이름을 바꿔줘야한다.

다만 밖에서 쓸 때 컬럼 값처럼 써줘야함.