mysql多表左连接查询例子参考

OA项目

mybatis参考

<select id="queryList" parameterType="map"        resultType="com.zhensheng.entity.BusAfficheEntityQuery">        <!-- 查询该用户的接收公告id -->        select        <include refid="afficheField" />        ,person.is_readed AS isRead,dict.label AS modelName        from bus_affiche AS        affiche LEFT JOIN bus_affiche_person AS person        on affiche.id =        person.affiche_id AND person.person_id = #{userId}        JOIN        sys_dict AS dict        on dict.id = affiche.affiche_type        where        affiche.id in        (        select distinct        receive.missive_id        from        bus_affiche_receive AS receive        <where>            <!-- 用户id -->            receive.condition_value =            <if test="userId != null and userId != ''">                #{userId}            </if>            <!-- 部门id -->            or receive.condition_value in            <if test="orgId != null and orgId != ''">                <foreach collection="orgId" item="oId" open="(" separator=","                    close=")">                    #{oId}                </foreach>            </if>            <!-- 自定义组id -->            or receive.condition_value in            <if test="groupId != null ">                <foreach collection="groupId" item="gId" open="(" separator=","                    close=")">                    #{gId}                </foreach>            </if>            and del_flag = '0'        </where>        order by create_date desc        )        AND affiche.is_recall = '0'        AND        affiche.del_flag = '0'        AND affiche.publish_status = '2'        order by        affiche.create_date desc        <if test="offset != null and limit != null">            limit #{offset}, #{limit}        </if>    </select>

生成sql语句如下

SELECT    affiche.id,    affiche.title,    affiche.content,    affiche.affiche_type,    affiche.publisher,    affiche.indate,    affiche.is_timing,    affiche.timing_time,    affiche.publish_status,    affiche.is_recall,    affiche.recall_time,    affiche.org_id,    affiche.create_by,    affiche.create_date,    affiche.update_by,    affiche.update_date,    affiche.remarks,    affiche.del_flag,    affiche.conditions,    person.is_readed AS isRead,    dict.label AS modelNameFROM    bus_affiche AS afficheLEFT JOIN bus_affiche_person AS personon affiche.id = person.affiche_idAND person.person_id = '1'JOIN sys_dict AS dict ON dict.id = affiche.affiche_typewhere     affiche.id IN (    SELECT DISTINCT        receive.missive_id    FROM        bus_affiche_receive AS receive    WHERE        receive.condition_value = '1'    OR receive.condition_value IN ('1', '3')    OR receive.condition_value IN ('1', '3')    AND del_flag = '0'    ORDER BY        create_date DESC)AND affiche.is_recall = '0'AND affiche.del_flag = '0'AND affiche.publish_status = '2'ORDER BY    affiche.create_date DESCLIMIT 1, 3

令外一个sql

SELECT    missive.id,    missive.title,    missive.publish_time,    missive.create_by_name AS publish,    reply_title,    reply.create_by_name,    reply.replyCountFROM    bus_missive_person personLEFT JOIN bus_missive missive ON person.missive_id = missive.idLEFT JOIN (    SELECT        count(id) replyCount,        reply_title,        create_by_name,        missive_person_id    FROM        (            SELECT                *            FROM                bus_missive_session            ORDER BY                reply_time DESC        ) i    GROUP BY        missive_person_id) reply ON person.id = reply.missive_person_idWHERE    (        send_person_id = '1'        AND send_del = '1'    )OR (    person_id = '1'    AND pernson_del = '1')

select gw.missive_id,missive.title,missive.publish_time,missive.create_by_name as publish,reply_title,reply.create_by_name,reply.replyCount,(case  when person.create_by='1' and person.is_readed='0' then '0'      when person.create_by='1' and person.is_readed='1' then '1'      else person.send_readed end) isBlod from (select receive.missive_id as missive_id from bus_missive_receive receive  where (receive.condition_key='Person' and receive.condition_value='1')   or  (receive.condition_key='Dept' and receive.condition_value='1')   or  (receive.condition_key='Group' and receive.condition_value='3')union  select id as missive_id from bus_missive  where create_by ='1') gwleft join bus_missive_person person on gw.missive_id = person.missive_idleft join bus_missive missive on person.missive_id = missive.idleft join (select count(id) replyCount,reply_title,create_by_name,missive_person_id  from (select * from bus_missive_session order by reply_time desc) i group by missive_person_id) reply on person.id = reply.missive_person_idwhere (send_person_id = '1') or (person_id = '1')//where (send_person_id = '1' and send_del='0') or (person_id = '1' and pernson_del='0')