[MariaDB]Select한 값 Insert 하기 + IF 아님x CASE WHEN THEN ELSE o 하는 쿼리 in Mybatis

MariaDB는 MySQL이랑 비슷하면서도 가끔 다르고..

데이터베이스의 세계가 그렇듯 복잡하지 않고 깔끔한 쿼리를 짜기란 너무 힘들다는 것..

 

원래는 INSERT 후 INSERT 한 값들을 UPDATE 시키는 방식으로 하려 했으나, 한 트랜잭션 내에서 수행해야 하는 문제가 있어서 INSERT 후 커밋이 되지 않아 UPDATE도 할 수 없는 문제가 있었다.

 

이번 INSERT SELECT WHERE, CASE WHEN 쿼리는 솔직히 엄청 맘에 드는 쿼리는 아니지만,

그래도 SELECT로 검색한 여러 값을 KEY로 INSERT 하기 + 조건에 따라서 CASE WHEN THEN을 써서 나머지 값들을 지정해주기 등을 해결했다는 점에 글을 써본다.

검색해도 MariaDB에 대한 쿼리 예제는 많지 않고 ㅠㅠ MySQL을 써야 하는 것이 마리아디비 유저들의 고충이라면 고충..(무료니깐 봐줘!)

 

그럼 지금부터 위의 쿼리를 보여드리도록 하겠다.

MariaDB(MySQL) INSERT INTO SELECT CASE WHEN THEN ELSE END

<해결해야 할 요구사항>

[MariaDB]Select한 값 Insert 하기 + IF 아님x CASE WHEN THEN ELSE o 하는 쿼리 in Mybatis

[사용자 테이블]에서 '사용자 아이디'를 가져와서 [연차 테이블]의 '사용자 아이디'에 넣고, 

[사용자 테이블]의 '입사일'을 구분하여 [연차 테이블]의 '휴가 일수'에 넣어줘야 한다.

1년 미만이면 월별 휴가를, 그 이상이면 15개와 근속 휴가를 따로 제공해줘야 한다.

 

 

<쿼리 작성 시나리오>

어찌하였든 궁극적으로 취해야 할 쿼리는 INSERT 이다.

[연차 테이블]에 값을 넣어주는 것이 근본적으로 해야하는 일이기 때문.

그리고 다른 테이블의 한 컬럼을 조건에 따라 넣어주는 것이기 때문에 SELECT FROM WHERE 를 쓸 것이다.

다음으로 [연차 테이블]의 다른 컬럼들은 조건에 따라 다르게 넣어줄 것이기 때문에, CASE WHEN THEN ELSE END 를 써 줄 것이다.

 

 

<쿼리>

INSERT
	 	   INTO VCAT_TABLE (
				USER_ID				-- 사용자 id
			  , TOT_VCATN_DAYCNT	-- 총 연차
			  , USE_VCATN_DAYCNT	-- 사용한 연차(0으로 할 것)
			  , YRYC_VCATN_DAYCNT	-- 기본 연차(1년 이하면 개월수에 따라, 이상이면 15개)
			  , CNWK_VCATN_DAYCNT	-- 근속 연차(1년 이하면 0개, 이상이면 년수에 따라)
			  , ETC_VCATN_DAYCNT	-- 기타 연차(0으로 할 것)

	 	   )
	 	 SELECT
				USER_ID
			  , CASE
			  		WHEN <![CDATA[ TIMESTAMPDIFF(YEAR, ECNY_DE, DATE_FORMAT(now(), '%Y%m%d')) < 1 ]]>
			  		THEN MONTH(CONVERT(ecny_de,DATE))-1 + ROUND(TIMESTAMPDIFF(DAY,ECNY_DE, DATE_FORMAT(now(),'%Y%m%d') )/365*15)
			  		ELSE 15 + FLOOR(TIMESTAMPDIFF(YEAR, ecny_de, DATE_FORMAT(now(), '%Y%m%d'))/2)
			    END
			  , 0
			  , CASE
			  		WHEN <![CDATA[ TIMESTAMPDIFF(YEAR, ECNY_DE, DATE_FORMAT(now(), '%Y%m%d')) < 1 ]]>
			  		THEN MONTH(CONVERT(ecny_de,DATE))-1 + ROUND(TIMESTAMPDIFF(DAY,ECNY_DE, DATE_FORMAT(now(),'%Y%m%d') )/365*15)
			  		ELSE 15
			    END
			  , CASE
			  		WHEN <![CDATA[ TIMESTAMPDIFF(YEAR, ECNY_DE, DATE_FORMAT(now(), '%Y%m%d')) < 1 ]]>
			  		THEN 0
			  		ELSE FLOOR(TIMESTAMPDIFF(YEAR, ecny_de, DATE_FORMAT(now(), '%Y%m%d'))/2)
			    END
			  , 0

		   FROM USER_TABLE
	 	  WHERE USE_YN = 'Y'	--사용가능한 사용자 구분

 

<쿼리 설명>

<![CDATA[             ]]> 은 Mybatis에서 xml파일에 MariaDB 쿼리를 작성할 때, <> 태그와 혼동하지 않도록 알려주는 태그라고 보면 된다.

다음은 내가 사용한 MariaDB 함수 목록이다.

MONTH 함수는 날짜형식의 데이터에서 달만 꺼내주는 함수

CONVERT 함수는 스트링을 특정 형태로 바꿔주는 함수

ROUND는 반올림 함수

TIMESTAMPDIFF는 두 날짜 간의 차이를 알려주는 함수

(참고 :[MySQL, MariaDB] 날짜 차이, 년차 연도 분기 차이 구하기 쿼리 총정리TIMESTAMPDIFF 와 DATEDIFF 예제)

FLOOR는 버림 함수

 

이 쿼리를 '절대적으로 효율적인 쿼리'라고 생각하지 말고.. 참고해서 더 좋고 효율적인 쿼리를 떠올려보면 좋겠다.