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 modelName
FROM

    bus_affiche AS affiche
LEFT JOIN bus_affiche_person AS person
on affiche.id = person.affiche_id
AND person.person_id = '1'
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
        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 DESC
LIMIT 1, 3

令外一个sql

SELECT
    missive.id,
    missive.title,
    missive.publish_time,
    missive.create_by_name AS publish,
    reply_title,
    reply.create_by_name,
    reply.replyCount
FROM
    bus_missive_person person
LEFT JOIN bus_missive missive ON person.missive_id = missive.id
LEFT 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_id
WHERE
    (
        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') gw
left join bus_missive_person person on gw.missive_id = person.missive_id
left join bus_missive missive on person.missive_id = missive.id
left 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_id
where (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')

其他sql
1.
2.