mybatis中的语句应用--sql的多重if条件句--sql的时间转换--多字段对应同一张表同一字段连接处理--or语句的应用--sql中根据某字段值让另一个字段显示不同的值--多条插入

1. sql语句常见的多重if条件句；sql中Date类型数据取年月日操作；左连接，同一条数据中多个字段对应另一张表的同一字段处理；or语句和and语句的优先级，or语句的处理方法。

 <select id="***" resultType="com.***.***.biz.dto.***Dto">
        select
            d.id,
            d.project_doc_id,
            p.doc_no as doc_receive_num,
            date_format(doc_receive_time, '%y-%m-%d') as doc_receive_time,
            d.doc_receive_dept,
            s.name as doc_receive_dept_name,
            su.name as sign_user_name,
            p.doc_no as doc_send_num,
            date_format(doc_send_time, '%y%m%d') as doc_send_time,
            d.doc_send_dept,
            sc.name as doc_send_dept_name,
            case
        	when d.if_upload_enclosure = 0 then
        	'是'
        	when d.if_upload_enclosure = 1 then
        	'否'
        	end as ifUploadEnclosureStr
        from
        doc_receive_send d
        left join sys_company s on d.doc_receive_dept = s.id
        left join sys_company sc on d.doc_send_dept = sc.id
        left join project_doc p on d.project_doc_id = p.id
        left join sys_user su on d.sign_user_id = su.id
        <where>
            <if test="type == 0">
                d.doc_receive_dept = #{companyId}
                <if test="key != null and key.trim() != ''">
                    and
                    (d.doc_name like concat('%',#{key},'%')
                    or
                    d.doc_receive_num like concat('%',#{key},'%'))
                </if>
            </if>
            <if test="type == 1">
                d.doc_send_dept = #{companyId}
                <if test="key != null and key.trim() != ''">
                    and
                    (d.doc_name like concat('%',#{key},'%')
                    or
                    d.doc_send_num like concat('%',#{key},'%'))
                </if>
            </if>

            <if test="state == 0">
                and d.state = 0
            </if>
            <if test="state == 1">
                and d.state in (0,1)
            </if>
            <if test="startTime != null">
                and d.create_time &gt;= #{startTime}
            </if>
            <if test="endTime != null">
                and d.create_time &lt;= #{endTime}
            </if>
        </where>
        order by
        d.create_time desc
    </select>

2. MyBatis之插入多条

Boolean saves(@Param(value = "applyNum") String applyNum
			  @Param(value = "list") List<RequirementInfo> list);

    <insert id="saves" parameterType="com.sdy.resdir.biz.model.RequirementInfo">
        insert into requirement_info (apply_num, requirement_name, field_type)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{applyNum}, #{item.requirementName}, #{item.fieldType})
        </foreach>
    </insert>

<insert id="saves" parameterType="com.sdy.resdir.biz.model.RequirementInfo">
        insert requirement_info (requirement_name)
        <foreach collection="list" item="item" index="index" separator="union">
           select #{item.requirementName}
        </foreach>
    </insert>

Boolean addExpertMeetingBatch(@Param(value = "id") Long id,
                                 @Param(value = "addMeeTempDtos") List<AddMeeTempDto> addMeeTempDtos,
                                 @Param(value = "userId") Long userId);


<insert id="addExpertMeetingBatch" parameterType="com.shucha.projhigh.biz.dto.meeting.AddMeeTempDto">
       insert into expert_meeting (expert_id, meeting_id, template, create_time, create_user)
       values
       <foreach collection="addMeeTempDtos" item="item" index="index" separator=",">
           (#{item.expertId}, #{id}, #{item.templateDetail}, now(), #{userId})
       </foreach>
   </insert>

3. sql中的foreach查询

	<select id="getManageFlowConfigPage" resultType="com.shucha.datahub.biz.dto.flowconfigdto.FlowConfigManageDTO">
        select
        d.id,
        d.flow_code,
        d.flow_node,
        d.name,
        d.describes,
        d.create_time,
        d.type
        from dh_apply_flow_config_manage d
        <where>
            d.create_user_id in
            <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            <if test="nameOrCode != null and nameOrCode != ''">
                and d.name like concat('%', #{nameOrCode} , '%')
                or
                d.flow_code like concat('%', #{nameOrCode} , '%')
            </if>
        </where>
        order by d.create_time desc
    </select>

4. sql语句中case then的使用

select a.dept_code, a.dept_name,
        sum(
        case when b.id is null or b.proj_state = 6 then 0 else 1 end
        ) as project_num
        from bas_dept_info a
        left join project b on b.belong_dept like concat(a.dept_code, '%')
        where a.dept_level = 1 and is_delete != 1
        group by a.dept_code, a.dept_name

<select id="getProjectPage" resultType="com.xxx.xxx.biz.dto.ProjectPageDto">
select
        p.id,
        p.proj_name,
        p.proj_amount,

        case
        when fund_source = 0 then
        '财政资金'
        when fund_source = 1 then
        '自筹资金'
        when fund_source = 2 then
        '上级补助'
        when fund_source = 3 then
        '其他'
        end as fundSourceStr,

        case
        when proj_type = 0 then
        '新建项目'
        when proj_type = 1 then
        '续建项目'
        when proj_type = 2 then
        '运维服务类项目'
        when proj_type = 3 then
        '其他'
        end as projTypeStr,

        p.build_dept,
        b.dept_name as belongDeptStr,

        p.proj_state,
        case
        when proj_state = 0 then
        '立项备案'
        when proj_state = 1 then
        '合同备案'
        when proj_state = 2 then
        '系统备案'
        when proj_state = 3 then
        '验收通过'
        when proj_state = 4 then
        '验收不通过'
        when proj_state = 5 then
        '废弃待审'
        when proj_state = 6 then
        '已废弃'
        when proj_state = 7 then
        '废弃不通过'
        end as projStateStr

        from
        project p
        left join bas_dept_info b on p.belong_dept = b.dept_code
        <where>
            <if test="projName != null and projName.trim() != ''">
                and p.proj_name like concat('%',#{projName},'%')
            </if>
            <if test="projType != null">
                and p.proj_type = #{projType}
            </if>
            <if test="projState != null">
                and p.proj_state = #{projState}
            </if>

            <if test="deptCode != null and deptCode.trim() != ''">
                and p.belong_dept = #{deptCode}
            </if>
            <if test="deptCode == null || deptCode.trim() == ''">
                <if test="belongDept != null and belongDept.trim() != ''">
                    and p.belong_dept = #{belongDept}
                </if>
            </if>
        </where>
        order by
        p.id desc
    </select>
<select id="getStoryPage" resultType="com.***.***.biz.dto.RdCollectStoryDTO">
        select
            c.id,
            c.res_id,
            r.res_name,
            r.res_source_dept,
            r.res_state,
            r.modify_time,
            r.res_power,
            r.res_type,
            r.is_release,
            r.apply_frequency,
            r.look_frequency,
            r.collection_situation,
            case
            when r.collection_situation = 1  and
                exists (select d.id from rd_resource_application_detail d where d.res_id = r.id and d.dept_id = #{ownDeptId} and d.state != 5)
            then 1
            when r.collection_situation = 2 and
                exists (select o.id from rd_res_for_online o where o.res_id = r.id and o.state != 5)
            then 1 else 0 end as if_apply
        from
            rd_collect_story c left join rd_resource_dir r on c.res_id = r.id
        <where>
            c.dept_id = #{ownDeptId}
        </where>
        order by c.create_time desc, c.id desc
    </select>

5. 伪表查询

<select id="getAllApiPage" resultType="com.shucha.datahub.biz.dto.apidto.AllApiPageDTO">
        select * from (
        select
        a.id,
        a.api_name,
        a.api_code,
        a.access_protocol,
        a.request_method,
        case
        when a.access_protocol = 0 then
        'soap'
        when a.access_protocol = 1 and request_method = 0 then
        'GET'
        when a.access_protocol = 1 and request_method = 1 then
        'POST'
        end as request_method_str,
        a.update_time,
        a.create_user_id,
        u.account_name as create_user_name,
        a.api_explain,
        a.api_state,
        case
        when exists (select ao.id from dh_apply_order ao where ao.apply_user_id = #{userId} and ao.api_code = a.api_code and ao.apply_result != 2)
        then 0 else 1 end as if_apply
        from
        dh_api_base_data a left join dh_user u on a.create_user_id = u.id
        <where>
            api_state = 1
            <if test="nameOrCode != null and nameOrCode.trim() != ''">
                and (a.api_name like concat('%',#{nameOrCode},'%')
                or
                a.api_code like concat('%',#{nameOrCode},'%'))
            </if>
            <if test="userName != null and userName.trim() != ''">
                and u.account_name like concat('%',#{userName},'%')
            </if>
        </where>
        ) one
        <where>
            <if test="ifApply != null">
                if_apply = #{ifApply}
            </if>
        </where>
        order by
        one.update_time desc
    </select>

6. 数据中某个字段出现多值对应

<select id="getPoliceForcePage" resultType="com.shucha.signalnotification.biz.dto.webdto.UserInfoPageDTO">
        select
        u.id,
        u.user_name,
        u.name,
        u.phone,
        u.belong_dept,
        o.name as dept_name,
        t.role_name,
        u.state
        from user_info u
        left join organization_manage o on u.belong_dept = o.id
        left join (select a.id, GROUP_CONCAT(c.name) as role_name from user_info a
        left join role_use b on b.user_id = a.id
        left join role c on c.id = b.role_id
        group by a.id) t on t.id = u.id
        <where>
            <if test="userNameOrName != null and userNameOrName.trim() != ''">
                and (u.name like concat('%', #{userNameOrName} , '%')
                or
                u.user_name like concat('%', #{userNameOrName} , '%'))
            </if>
            <if test="belongDept != null">
                and u.belong_dept =#{belongDept}
            </if>
        </where>
        order by u.create_time desc
    </select>

7.时间重叠，生日获取

<select id="getRandExpertList" resultType="com.shucha.projhigh.biz.dto.expert.ExpertDataListDto">
        select
        e.id,
        e.name,
        e.phone,
        floor(datediff(current_date, substring(e.id_card, 7, 8)) / 356.25) as age,
        e.sex,
        case when e.sex = 0 then '女' when e.sex = 1 then '男' end as sex_str,
        e.grade,
        case
        when e.grade = 0 then '国家级'
        when e.grade = 1 then '省部级'
        when e.grade = 2 then '市县级'
        when e.grade = 3 then '顾问级'
        end as grade_str,
        e.areas_of_expertise,
        e.qualifications,
        case when e.qualifications = 0 then '无' when e.qualifications = 1 then '有' end as qualifications_str
        from expert_data e
        <where>
            e.state = 1 and e.qualifications = 1 and
            (select
            case when
            exists (
            select em.id from expert_meeting em left join meeting m on em.meeting_id = m.id
            where
            ((concat(m.date, ' ', m.start_time) &lt;= #{startTime} and #{startTime} &lt;= concat(m.date, ' ', m.end_time))
            or
            (concat(m.date, ' ', m.start_time) &lt;= #{endTime} and #{endTime} &lt;= concat(m.date, ' ', m.end_time))
            or
            (concat(m.date, ' ', m.start_time) &gt;= #{startTime} and #{endTime} &gt;= concat(m.date, ' ', m.start_time))
            or
            (concat(m.date, ' ', m.end_time) &gt;= #{startTime} and #{endTime} &gt;= concat(m.date, ' ', m.end_time)))
            and em.expert_id = e.id
            )
            then 1 else 0 end as if_cover
            ) = 0
        </where>
        order by rand() limit #{number}
    </select>

8. 一个字段传入多个用“,”分割的值进行对比查询

SELECT * FROM `rd_resource_dir` WHERE  FIND_IN_SET (res_name, 'jcy交换类资源测试,wz未归集发布,测试mqtoapi取消自动化')

9. 随机获取表中多少条数据

<select id="getDataHot" resultType="com.shucha.datagovern.biz.dto.statistics.StandardBasePageDTO">
        select
        s.id,
        s.type,
        s.code
        from bz_standard_release s where s.state = 1 order by  rand() limit 30
    </select>

10. 日期统计sql
    

<select id="getReleaseTrend" resultType="com.shucha.datagovern.biz.dto.statistics.DayDataDTO">
        select DATE_FORMAT(a.date, '%Y-%m-%d') as day, IFNULL(b.count, 0) as total
        from (
        select a.Date
        from (
        select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
        from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
        ) a
        where a.Date between #{startTime} and #{endTime}
        ) a
        left join (
            select count(*) as count, DATE_FORMAT(create_time, '%Y-%m-%d') as date
            from `bz_standard_release`
            where state = 1
                and DATE_FORMAT(create_time, '%Y-%m-%d') between #{startTime} and #{endTime}
            group by date
        ) b
        on a.date = b.date
        order by a.date asc;
    </select>

11. find_in_set和逗号分隔的字符串之间的使用

List<SysDeptDto> getSysDeptList(@Param(value = "name") String name,
                                    @Param(value = "typeList") List<String> typeList);
                                    
<select id="getSysDeptList" resultType="com.shucha.projhigh.biz.dto.dept.SysDeptDto">
        select
        s.id,
        s.name,
        s.abbreviation,
        s.type
        from sys_dept s
        <where>
            <if test="name != null and name.trim() != ''">
                s.name like concat('%',#{name},'%')
            </if>
            <if test="typeList.size > 0">
                and
                <foreach collection="typeList" index="index" item="item" open="(" separator="or" close=")">
                    find_in_set(#{item}, s.type)
                </foreach>
            </if>
        </where>
        order by s.create_time desc
    </select>