记录学习中用到的sql语句

1、根据一张表更新另一张表的数据

update t_table t1
set a = t2.a,
	b = t2.b
from t_table t2
where t1.name=t2.name

2、批量更新

mybatis批量更新

<update id="updateBatch" parameterType="java.util.List">
        update mydata_table
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="status =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                     when id=#{item.id} then #{item.status}
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.id,jdbcType=BIGINT}
        </foreach>
    </update>

对应sql语句：

update mydata_table 
    set status = 
    case
        when id = #{item.id} then #{item.status}//此处应该是<foreach>展开值
    end
    where id in (...);

3、pgsql时间戳查询

select extract(epoch FROM now())

4、SQL高效查询两个表不同的数据

select * from B where (select count(1) from A where A.ID = B.ID) = 0