앱)sql 쿼리 mysql mybatis 샘플

<select id="select1" resultMap="MasterVO">
         SELECT
                 COUNT(*)
                 , IFNULL((SELECT CD_NM FROM COMN_CD WHERE CD_GRP = 'CCC_CD' AND CD = CC_CD), '') AS statusCode
                , CASE WHEN CD = 'M'
                             THEN
                                         CASE WHEN USE_CD = '0' THEN 'POC'
                      ELSE 'MMM'
                   END
                 WHEN CD ='T' || CD="TE" THEN 'TEST'
                 WHEN CD ='N' THEN 'COMMERCIAL'
                 ELSE ''
            END AS typeCodeName
            , DATE_FORMAT(CRT_DT, '%Y-%m-%d %H:%i:%s') AS createDatetime
            FROM
           <where>
                   <if test="id ! = null and id !=''">
                      AND ID LIKE CONCAT('%', UPPER(#{id}), '%')
                  </if>
                  <if test="cd ! = null and cd !=''">
                    <choose>
                             <when test="cd == 'K'.toString()">
                           AND CD = #{cd}
                               </when>
                               <otherwise>
                                   <if test="cd == "M".toString()">
                          AND CD= #{cd}
                                    </if>
                             </otherwise>
                        </choose>
                   </if>
                   <if test="registeredDatetimeFrom ! = null and registeredDatetimeFrom !=''">
                    AND CRT_DT <![CDATA[>=]]> DATA_FORMAT(CONCAT(#{registeredDatetimeFrom}, '000000'), '%Y%m%d%H%i%s')
                   </if>
                   <if test="registeredDatatimeTo ! = null and registeredDatetimeTo !=''">
                     AND CRT_DT <![CDATA[<=]]> DATA_FORMAT(CONCAT(#{registeredDatetimeTo, '235959'), '%Y%m%d%H%i%s')
                    </if>
                    <if test="registeredDatetimeFrom != null and registeredDatetimeTo != null">
                        AND CRT_DT BETWEEN DATE_FORMAT(CONCAT(#{registeredDatetimeFrom}, '00:00:00'), '%Y%m%d%H%i%s') AND DATE_FORMAT(CONCAT(#{registeredDatetimeTo}, '23:59:59'), '%Y%m%d%H%i%s')
                     </if>
                     <choose>
                            <when test="sortName != null and sortNAme != ''">
                     <if test="sortName == 'Id'.toSTring()">
                      ORDER BY ID
                      </if>
                   </when>
                   <otherwise>
                   ORDER BY ...
                   </otherwise>
                 </choose>
                    AND SVC_OPEN_DT BETWEEN DATE_ADD(NOW(), INTERVAL- #{interval} month) and NOW()
                    AND SVC_FG IS NULL
                    AND ID1 = SUBSTRING_INDEX(#{ids}, '/', 1)
                    AND DATE_FORMAT(MDFY_DT, '%Y%m%d') <![CDATA[>=]]> DATE_SUB(CURDATE(), INTERVAL 3 DAY)
                    ORDER BY DT DESC
                    <if test="countPerPage ! =0 ">
                            LIMIT #{startRowIndex}, #{countPerPage}
                    </if>
                 
          </where>
</select>


<select id="select" parameterType="java.util.HashMap">
     SELECT
            CONCAT('(', REPLACE(REPLACE(CD_NM, '(', ''), ')',''),')' )
           , CONCAT(round((SUM(CNT)/TOTALCOUNT * 100),2),'%') AS percentage
          , CONCAT('MN', LPAD(CAST(CAST(RIGHT(IFNULL(MAX(MN_ID), 'MN0000'), 4) AS UNSIGNED) + 1 AS CHAR), 4, '0')) AS value
        FROM
        WHERE
          CRT_DT BETWEEN DATE_SUB(RIGHT(LEFT(#{value}, 11), 8), INTERVAL 1 DAY) AND DATE_ADD(RIGHT(LEFT(#{value}, 11), 8), INTERVAL 1 DAY)
           AND DATE_FORMAT(CRT_DT, '%Y%m%d') <![CDATA[>=]]> DATE_SUB(CURDATE(), INTERVAL 3 DAY)
          <include refid="whereclause" />
</select>     

<sql id="whereclause">

       <if test="(ID != null and ID != '') or (NM != null and NM != '') or (desc !=null and desc != '')">
       </if>
       <if test='code.equals("K")'>
       </if>
       <if test="registeredDatetimeFrom != null and !''.equals(registeredDatetimeFrom)">
        </if>
</sql>

<select id="" parameterType="String" resultType="VO">
         TO_DAYS(NOWS()) - TO_DAYS(END_DD) AS CAL_DD
</select>

<select id="selectBatchDatetime" resultType="String">
         SELECT
                      CONCAT(DATE_FORMAT(NOW(), '%Y-%m-%d'), 'GMT', DATE_FORMAT(NOW(), '%H:%i')) AS batchDt
            FROM DUAL
</select>


<select id="xxx" parmeterType="map" resultType="hashmap">
SELECT COUNT(CASE WHEN A.YN='N' THEN 1 END) AS N_CNT, COUNT(CASE WHEN A.YN='Y' THEN 1 END) AS Y_CNT
FROM A;
</select>

<insert id="addMember" parameterType="VO">
INSERT IGNORE INTO MEMBER
                        (
                         <trim suffixOverrides=",">
                            ID
                          , NM
                          , CRTR_ID
                          , CRT_DT
                          , MDFR_ID
                          , MDFY_DT
                          </trim>
                        )
                       VALUES
                        (
                          <trim suffixOverrides=",">
                              #{id}
                            , #{nm}
                            , #{creatorId}
                            , SYSDATE()
                            , #{modifierId}
                            , SYSDATE()
                          </trim>
                         )

</insert>

<insert id="" useGeneratedKeys="true" parameterType="VO">
        <selectKey resultType="string" keyProperty="seq" order="BEFORE">
        SELECT CONCAT(LPAD(CAST(CAST(RAND()*1000000000 AS UNSIGNED) AS CHAR),8,'0'), "-", DATE_FORMAT(NOW(), '%Y%m%d'))
         </selectKey>
         INSERT INTO
         (
          OPRN_HIST_ID
          )
          VALUES
          (
           #{seq}
          )

<update id="">
      INSERT INTO
                    VALUES
                     ON DUPLICATE KEY UPDATE

</update>


<delete id="delete" parameterType="map">
   DELETE FROM GRP
   WHERE USR_ID IN
    <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
             #{item}
     </foreach>
</delete>


JOIN TYPE
1. LEFT OUTER JOIN
2. INNTER JOIN

INSERT TYPE(중복키관리)
1. INSERT IGNORE
기존 레코드가 남아 있음
기존 레코드의 auto_increment 값은 변하지 않음
2. REPLACE INTO
기존 레코드가 삭제되고, 신규 레코드가 INSERT됨
따라서 auto_increment의 값이 변경됨
3. ON DUPLICATE UPDATE
INSERT IGNORE의 행동 포함
중복키 오류 발생시, 사용자가 UPDATE될 항목들을 지정할 수 있음