|
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。
AND
company_id = #{companyId}
1=2
分组、根据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`
WHEN #{id} THEN COUNT(1)
ELSE
0
END assetsCount,
CASE t.`asset_min_class`
WHEN #{id} THEN SUM(original_value)
ELSE
0
END originalSum,
CASE t.`asset_min_class`
WHEN #{id} THEN SUM(net_value)
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
and p.`id` = #{planId}
and p.calibration_end_time ; #{beginTime}
and p.calibration_end_time ; #{endTime}
AND t.`is_deleted` = 0
AND t.`to_void` = 0
AND (
t.asset_max_class = #{assetMaxClass1}
OR t.asset_max_class = #{assetMaxClass2}
OR t.asset_max_class = #{assetMaxClass3}
)
GROUP BY
t.calibration_result,
t.`asset_min_class`
|