常用xml文件的sql语句

1、某个字段为空时把另一个字段的值赋值给这个字段 CASE 

SELECT
( CASE WHEN a.leader IS NULL THEN c.leader_name ELSE a.leader END ) AS leader,
FROM
zlzdgzfp_formdata_city a
LEFT JOIN zlzdgzfp_admin_config c ON a.city_id = c.dept_id

2、根据某个字段进行排序，且此排序字段为整数但数据库存储时使用字符串类型存储时，需要先转为整型然后排序  CAST

select a.dept_name,
       a.dept_id
from zlzdgzfp_formdata_city a
         left join zlzdgzfp_admin_config b on a.dept_id = b.dept_id
where a.year = #{year}
order by CAST( b.order_num AS SIGNED ) ASC

3、根据某个字段模糊查询 like concat

<if test="cityName != null  and cityName != ''">and city_name like concat('%', #{cityName}, '%')</if>

4、根据某个字段聚合结果，把相同的字段值拼接  GROUP_CONCAT

SELECT
    s.dept_name,
    GROUP_CONCAT( s.user_name ORDER BY s.id SEPARATOR ', ' ) AS user_name,
    GROUP_CONCAT( COALESCE ( s.score, '' ) ORDER BY s.id SEPARATOR ', ' ) AS score,
    GROUP_CONCAT( s.step_id ORDER BY s.id SEPARATOR ', ' ) AS step_id
FROM
    zlzdgzfp_score s
        LEFT JOIN zlzdgzfp_admin_config c ON s.dept_id = c.dept_id
WHERE
    s.process_id = #{process_id}
GROUP BY
    s.dept_id
ORDER BY
    CAST( c.order_num AS SIGNED ) ASC

5、某个字段值为null的时候，转为空字符串并拼接  COALESCE 

GROUP_CONCAT( COALESCE ( s.score, '' ) ORDER BY s.id SEPARATOR ', ' ) AS score

6、某个字段值存在于数组中 in

select * from sys_dept a
where a.process_id = #{processId}
and a.step_id = #{stepId}
<if test="deptIds != null and deptIds.size() > 0">
    AND a.dept_id IN
    <foreach item="item" index="index" collection="deptIds" open="(" separator="," close=")">
        #{item}
    </foreach>
</if>

7、根据id批量更新

UPDATE zlzdgzfp_score
SET
<trim prefix="(" suffix=")" suffixOverrides=",">
    <foreach collection="list" item="item" index="index" separator=",">
        <trim prefix="SET" suffixOverrides=",">
            <if test="item.score != null">score = #{item.score},</if>
            <if test="item.status != null">status = #{item.status},</if>
        </trim>
        WHERE id = #{item.id}
    </foreach>
</trim>

8、根据id批量删除

delete from zlzdgzfp_score where id in
<foreach item="id" collection="array" open="(" separator="," close=")">
    #{id}
</foreach>