[Mybatis] 쿼리문

 

 

//리스트 길이 만큼 반복해서 COUNT
<select id="selectDataVariableDeleteCheck" parameterType="DataVariableVO" resultType="int">
	<foreach collection="varSeqList" item="varSeq" separator=" UNION ALL ">
		SELECT COUNT(*)
		FROM public.cdp_dataset_request_variable
		WHERE var_seq = #{varSeq}
	</foreach>
</select>

// COUNT값 개수에 따라 1, 0 출력
<choose>
	<when test="count(varSeqList) > 0">
		<!-- If varSeqList is not empty -->
		<foreach collection="varSeqList" item="varSeq" separator=" UNION ALL ">
			SELECT COUNT(*) AS count_result
			FROM public.cdp_dataset_request_variable
			WHERE var_seq = #{varSeq}
		</foreach>
		<if test="count(varSeqList) > 1">
			<!-- If more than one result, sum the count_result values -->
			SELECT SUM(count_result) AS final_result
			FROM (
				<foreach collection="varSeqList" item="varSeq" separator=" UNION ALL ">
					SELECT COUNT(*) AS count_result
					FROM public.cdp_dataset_request_variable
					WHERE var_seq = #{varSeq}
				</foreach>
			) AS counts
		</if>
	</when>
	<otherwise>
		<!-- If varSeqList is empty, return 0 -->
		SELECT 0 AS final_result
	</otherwise>
</choose>

//리스트 데이터와 일치하는 row 삭제
<delete id="deleteDataVariables" parameterType="DataVariableVO">
	DELETE FROM public.cdp_data_variable
	WHERE varSeq IN
	<foreach collection="varSeqList" item="varSeq" open="(" separator="," close=")">
		#{varSeq}
	</foreach>
</delete>

 

버전 업을 위한 쿼리

/*col1인 데이터 col2값 1 증가시키기*/
UPDATE public.sample
SET col2 = (col2 + 1)
WHERE col1 = #{col1}
  AND col2 = (
    SELECT COALESCE(MAX(col2), 0)
    FROM sample
    WHERE col1 = #{col1}
);