SQL-技巧总结

一条SQL更新多条记录

MySql

update persons
set name = case
when id = '张三' then '张三丰'
when id = '李四' then '李斯'
when id = '王五' then '王雾' 
end
where name in ('张三','李四','王五')

在MyBatis中应该这样使用，比如批量更新persons表的name字段

<update id="updateBatch" parameterType="list">
    update persons
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="name =case" suffix="end,">
            <foreach collection="list" item="i" index="index">
                <if test="i.nameStr!=null">
                    when id=#{i.id} then #{i.nameStr}
                </if>
            </foreach>
        </trim>
    </trim>
    where
    <foreach collection="list" separator="or" item="i" index="index">
        id=#{i.id}
    </foreach>
</update>

一条SQL插入多条数据

MySQL

insert into persons 

(id_p, lastname , firstName, city )

values

(200,'haha' , 'deng' , 'shenzhen'),

(201,'haha2' , 'deng' , 'GD'),

(202,'haha3' , 'deng' , 'Beijing');

Oracle

insert all
into RAW_AMORT_SCHEDULE  (BOND_CODE,AMORTIZE_DATE,AMORTIZE_TYPE,AMORTIZE_RATE,REMARKS) values('1','1','1','1','1')
into RAW_AMORT_SCHEDULE  (BOND_CODE,AMORTIZE_DATE,AMORTIZE_TYPE,AMORTIZE_RATE,REMARKS) values('2','2','2','2','2')
into RAW_AMORT_SCHEDULE  (BOND_CODE,AMORTIZE_DATE,AMORTIZE_TYPE,AMORTIZE_RATE,REMARKS) values('3','4','4','4','3')
SELECT 1 FROM DUAL 

在mybatis中应该这样使用

<insert id="insertRecordBatch" parameterType="java.util.List">  
    insert all  
    <foreach collection="list" item="item" index="index" separator=" " >
    	into RAW_AMORT_SCHEDULE(BOND_CODE,AMORTIZE_DATE,AMORTIZE_TYPE,AMORTIZE_RATE,REMARKS)
        values (#{item.bondCode},to_date(#{item.amortizeDate},'YYYYMMDD'),#{item.amortizeType},#{item.amortizeRate},#{item.remarks}) 
    </foreach>
    SELECT 1 FROM DUAL 
</insert> 

to_char()保留小数,千位分隔符

SELECT to_char(999999999999.99, 'FM999,999,999,999,999.000') AS QTY_1 from dual;

输出结果：999,999,999,999.990
注意事项：

• .后面的是要保留的小数位数,必须是0
• FM-去除空格

但是这样做当要处理的数字为0.99时,

SELECT to_char(0.99, 'FM999,999,999,999,999.000') AS QTY_1 from dual;

输出结果：.990
这样显然是不行的
解决方法

SELECT to_char(0.99, 'FM999,999,999,999,990.000') AS QTY_1 from dual;

输出结果：0.990

SELECT to_char(1111111111111111111111111111.99, 'FM999,999,999,999,990.000') AS QTY_1 from dual;

输出结果：########################
当要处理的数字的整数位数大于格式的整数位数时,就会出现这样的情况

SELECT to_char(1.99999, 'FM999,999,999,999,990.000') AS QTY_1 from dual;

输出结果：2.000
当要处理的数字的小数位数大于格式的小数位数时,对数字进行四舍五入