一、基本语法
CASE [COLUMN_NAME]
-- WHEN ['条件参数'] THEN ['COLUMN_NAME/显示内容']
-- WHEN ['条件参数'] THEN ['COLUMN_NAME/显示内容']
-- ......
-- ELSE ['COLUMN_NAME/显示内容']
-- END
二、使用场景
1、查询结果中，在select之后，from之前；
2、查询条件中，在where之后。
三、示例
<!-- 查询字段 -->
<sql id="columns">
P.PAPER_BOOK_ID, P.PAPER_BOOK_NAME, P.PRICE AS PRICE,
<!-- select之后，from之前-->
(case when E.CARD_QUANTITY is not null then E.CARD_QUANTITY else 1 end)
AS CARD_COUNT, S.STOCK_NUM AS STOCK, (case when P.CATEGORY_NAME = '预售' then 2 else 1 end)
AS BOOK_TYPE, D.USE_FILTER, D.OPT_TIME
</sql>
<!-- 动态查询 -->
<sql id="select">
<where>
P.CP_ID <![CDATA[ <> ]]> 29
<if test="paperBookName != null and paperBookName !='' ">
and P.PAPER_BOOK_NAME like '%' || #{paperBookName,jdbcType=VARCHAR} ||'%'
</if>
<if test="paperBookId != null and paperBookId !=''">
and P.PAPER_BOOK_ID = #{paperBookId}
</if>
<if test="cardBegin != null">
<!--where之后-->
and (case when E.CARD_QUANTITY is not null then E.CARD_QUANTITY
else 1 end) <![CDATA[ >= ]]> #{cardBegin}
</if>
<if test="cardEnd != null">
and (case when E.CARD_QUANTITY is not null then E.CARD_QUANTITY
else 1 end) <![CDATA[ <= ]]> #{cardEnd}
</if>
<if test="useFilter != null">
and D.USE_FILTER = #{useFilter}
</if>
</where>
</sql>
<!-- 分页组件拼装 -->
<sql id="pagesModuleLeft">
SELECT * FROM
(
SELECT A.*, ROWNUM RN from (
</sql>
<!-- 分页组件拼装 -->
<sql id="pagesModuleRight">
) A
)
WHERE RN >= ((${pageIndex}-1) * ${pageSize}) + 1 and RN <= ${pageIndex} * ${pageSize}
</sql>
<!---动态查询-->
<select id="queryDBookByCondition" resultMap="BaseResultMap"
parameterType="com.xx.xx.book.dto.Dto">
<if test="pageIndex != null "><include refid="pagesModuleLeft"/></if>
select rownum rowseq, <include refid="columns"/> from
PAPERBOOK_STOCK S
left join PAPERBOOK P on S.PAPER_BOOK_ID = P.PAPER_BOOK_ID
left join PAPERBOOK_EXCHANGE_CARD E on P.PAPER_BOOK_ID = E.PAPER_BOOK_ID
left join D_BOOK D on P.PAPER_BOOK_ID = D.PAPER_BOOK_ID <include refid="select"/>
order by P.CREATE_TIME desc
<if test="pageIndex != null "><include refid="pagesModuleRight"/></if>
</select>
四、个人电商中台开源项目
square-pavilion（四方阁）
Square pavilion is a cube project for e-commerce.（四方阁是一个为电商而生的中台项目）。 在玄幻小说中，总有一些比较牛逼且左右逢源于各个势力之间的中立组织，这些组织通常带有“阁”字。因此本项目取名“四方阁”，取包容并蓄，吸取百家之长之意。欢迎有兴趣的小伙伴们一起努力，把这个项目完善、推广。 Github square-pavilion
