sql中的动态查询choose (when, otherwise

sql中的动态查询choose (when, otherwise)标签

有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个。而使用if标签时，只要test中的表达式为 true，就会执行 if 标签中的条件。MyBatis 提供了 choose 元素。if标签是与(and)的关系，而 choose 是或(or)的关系。

choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when 的条件都不满则时，则执行 otherwise 中的sql。类似于Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

        <where>
            <if test="userAuth.companyIsAll == 0">
                AND
                <choose>
                    <when test="userAuth.authCompanyIds != null and userAuth.authCompanyIds.size() > 0">
                        <foreach collection="userAuth.authCompanyIds" item="companyId" open="(" separator="or"
                                 close=")">
                            company_id = #{companyId}
                        </foreach>
                    </when>
                    <otherwise>
                        1=2
                    </otherwise>
                </choose>
            </if>
      </where>

分组、根据id求count或者sum

SELECT 
  CASE
    t.`asset_min_class` 
    WHEN "11" 
    THEN COUNT(1) 
    WHEN "12" 
    THEN COUNT(1) 
    WHEN "13" 
    THEN COUNT(1) 
    WHEN "14" 
    THEN COUNT(1) 
    WHEN "15" 
    THEN COUNT(1) 
    WHEN "16" 
    THEN COUNT(1) 
    ELSE 0 
  END assetsCount,
  CASE
    t.`asset_min_class` 
    WHEN "11" 
    THEN SUM(original_value) 
    WHEN "12" 
    THEN SUM(original_value) 
    WHEN "13" 
    THEN SUM(original_value) 
    WHEN "14" 
    THEN SUM(original_value) 
    WHEN "15" 
    THEN SUM(original_value) 
    WHEN "16" 
    THEN SUM(original_value) 
    ELSE 0 
  END originalSum,
  t.calibration_result AS calibrationResult,
  t.`asset_min_class` AS assetMinClass,
  t.`asset_min_class_name` AS assetMinClassName 
FROM
  `task` t 
  RIGHT JOIN plan p 
    ON p.`id` = t.`task_id` 
WHERE p.`task_status` = 8 
  AND t.`is_deleted` = 0 
  AND t.`to_void` = 0 
GROUP BY t.calibration_result,
  t.`asset_min_class` 


#对应上面的sql

        SELECT
            CASE t.`asset_min_class`
            <foreach collection="strList" item="id" separator=" ">
                WHEN #{id} THEN COUNT(1)
            </foreach>
            ELSE
                0
            END assetsCount,

            CASE t.`asset_min_class`
            <foreach collection="strList" item="id" separator=" ">
                WHEN #{id} THEN SUM(original_value)
            </foreach>
            ELSE
            0
            END originalSum,

            CASE t.`asset_min_class`
            <foreach collection="strList" item="id" separator=" ">
                WHEN #{id} THEN SUM(net_value)
            </foreach>
            ELSE
            0
            END netSum,

             t.calibration_result as calibrationResult,
             t.`asset_min_class` as assetMinClass,
             t.`asset_min_class_name` as assetMinClassName
            FROM
                `task` t
            RIGHT JOIN plan p ON p.`id` = t.`task_id`
            WHERE
                p.`task_status` = 8
            <if test="planId != null">
                and p.`id` = #{planId}
            </if>
            <if test="beginTime != null">
                and p.calibration_end_time &gt; #{beginTime}
            </if>
            <if test="endTime != null">
                and p.calibration_end_time &lt; #{endTime}
            </if>
            AND t.`is_deleted` = 0
            AND t.`to_void` = 0
            AND (
                <if test="assetMaxClass1 != null">
                    t.asset_max_class = #{assetMaxClass1}
                </if>
                <if test="assetMaxClass2 != null">
                    OR t.asset_max_class = #{assetMaxClass2}
                </if>
                <if test="assetMaxClass3 != null">
                    OR t.asset_max_class = #{assetMaxClass3}
                </if>
            )
            GROUP BY
                t.calibration_result,
                t.`asset_min_class`