본문 바로가기
개발자의 공부방/프로젝트 일지

프로젝트] Mybatis Sql

by 쌈빡한 쥬니준 2020. 6. 18.
반응형
<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 를 통해서 이름을 바꿔줘야한다.

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

 

반응형

댓글0