MYSQL CASE WHEN THEN ELSE END 활용 예시

agnusdei1207 2022. 7. 4. 18:03

SMALL

<select id="selectStrangeCount" parameterType="cmmnDefaultVO" resultType="int">
/* LogLog.selectCount */
<![CDATA[  
    SELECT   COUNT(logSeq)	cnt  
      FROM   (SELECT   LOG_SEQ																									 logSeq
                   ,   LOG_ID																									 logId
                   ,   CASE
                WHEN   LOG_DIVN = 'ma'
                THEN   (SELECT 	FAIL_CNT FROM t_ad_user tau WHERE tau.ID = tll.LOG_ID) 
                WHEN   LOG_DIVN = 'ft'
                THEN   (SELECT 	ME_FAIL_CNT FROM t_member tm WHERE tm.ME_ID = tll.LOG_ID) 
                ELSE   ''
                 END   failCnt

                FROM   t_log_log	tll 

               WHERE   1 = 1    
       ]]>     
        <include refid="Where" />		 	 
        <![CDATA[
            GROUP BY	logId  
              HAVING	failCnt > 5
                   ) 	A 
              ]]>  
</select>

1. 서브쿼리를 활용하여 리턴된 조회 결과를 다시 한 번 WHEN 조건을 건다.

2. alias 빼먹지 않도록 유의하세요.

LIST