Mysql&&Oracle

Mysql&&Oracle

mybatis通用代码：

# 超过1000条条件的foreach循环查询
SELECT * FROM table
WHERE (1,id) IN
<foreach collection="idList" index="index" open="(" close=")" item="item" separator=",">
(1,#{item})
</foreach>

根据id 批量更新多个字段数据 
    <update id="updateList" parameterType="java.util.List" >
        update allocation_details
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="collect_num =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.collectNum != null ">
                        when id = #{i.id} then #{i.collectNum}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" separator="or" item="i" index="index" >
            id = #{i.id}
        </foreach>
    </update>	

# 获取模板信息联动数据集合
mapper方法:	MsgTemplateDTO findTemplateById(@Param("id") Long id);
对应的xml sql:	
	<select id="findTemplateById" resultMap="dtoMap" parameterType="long">
        select * from msg_template where id = #{id}
    </select>
	
	<resultMap id="dtoMap" type="com.wehgc.remote.message.dto.req.MsgTemplateDTO">
        <id column="id" property="id"/>
        <result column="code" property="code"/>
        <result column="title" property="title"/>
        <result column="sub_title" property="subTitle"/>
        <result column="send_name" property="sendName"/>
        <collection property="msgAttributesDTOList" ofType="com.wehgc.remote.message.dto.req.MsgAttributesDTO" javaType="java.util.List" column="id" select="com.wehgc.message.msg.mapper.MsgAttributesMapper.listByTempId"/>
    </resultMap>
	
联动集合:	
mapper方法:	List<MsgAttributesDTO> listByTempId(@Param("id") Long tempId);
对应的xml sql:	    
	<select id="listByTempId" resultMap="dataMap" parameterType="long">
        select * from msg_attributes where temp_id = #{id} and is_del = 0
    </select>
	
	<resultMap id="dataMap" type="com.wehgc.remote.message.dto.req.MsgAttributesDTO">
        <id column="id" property="id"/>
        <result column="code" property="code"/>
        <result column="name" property="name"/>
        <result column="value" property="value"/>
        <result column="sort" property="sort"/>
        <result column="temp_id" property="tempId"/>
        <collection property="msgAttributesStyleDTOList" ofType="com.wehgc.remote.message.dto.req.MsgAttributesStyleDTO" javaType="java.util.List" column="id" select="com.wehgc.message.msg.mapper.MsgAttributesStyleMapper.listByAttrId"/>
    </resultMap>
	
联动集合:	
mapper方法:  List<MsgAttributesStyleDTO> listByAttrId (@Param("id") Long attrId);	
对应的xml sql:
    <select id="listByAttrId" resultType="com.wehgc.remote.message.dto.req.MsgAttributesStyleDTO">
        select * from msg_attributes_style where attr_id = #{id} and is_del = 0
    </select>

共同sql:

# 去重
select distinct name from test_user;

# 查询数量
select count(1) from table;

# 排序null值排在最后
select * from table order by id desc nulls last;

# 查询某个字段存在多条数据并展示数据
select t.name,count(1) from sys_user t group by t.name having count(1) > 1;

oracle中在plsql编辑器

# 直接修改查询后的表
select t.*,t.rowid from table t;
select * from table_name for update;

# 查询只返回一条数据
select * from table where rownum = 1;

# 查询时间排序最新一条数据： 
select k.bookdate as bookDate from (select a.bookdate, row_number() over (partition by a.id by a.date desc) rn from table a where a.id=‘1000’）k where k.rn = 1;

Mysql中 可以使用plsql或是navicat

# 查询只返回一条数据
select * from table limit 1;