mapper中 复杂SQL 查询

１　定义实体　 type="Conversation" 　最好是java 实体也叫这个名字

    <resultMap type="Conversation" id="ConversationResult">

        <result property="externalUserid"    column="external_userid"    />
        <result property="followUser"    column="follow_user"    />
        <result property="name"    column="name"    />
        <result property="avatar"    column="avatar"    />

        <result property="owner"    column="owner"    />
        <result property="adminList"    column="admin_list"    />

        <result property="seq"    column="seq"    />
        <result property="corpid"    column="corpId"    />
        <result property="msgtype"    column="msgtype"    />
        <result property="msgid"    column="msgid"    />
        <result property="action"    column="action"    />
        <result property="from"    column="from"    />
        <result property="tolist"    column="tolist"    />
        <result property="roomid"    column="roomid"    />
        <result property="msgtime"    column="msgtime"    />
        <result property="mark"    column="mark"    />
        <result property="text"    column="text"    />
        <result property="image"    column="image"    />
        <result property="voice"    column="voice"    />
        <result property="emotion"    column="emotion"    />
        <result property="revoke"    column="revoke"    />
        <result property="meeting"    column="meeting"    />
        <result property="docmsg"    column="docmsg"    />
        <result property="news"    column="news"    />
        <result property="calendar"    column="calendar"    />
        <result property="card"    column="card"    />
        <result property="agree"    column="agree"    />
        <result property="link"    column="link"    />
        <result property="chatrecord"    column="chatrecord"    />
        <result property="vote"    column="vote"    />
        <result property="collect"    column="collect"    />
        <result property="redpacket"    column="redpacket"    />
        <result property="video"    column="video"    />
        <result property="file"    column="file"    />
        <result property="location"    column="location"    />
    </resultMap>

COALESCE  合并 多个输出结果为一个字段 由左到右优先级

 IF 判断第一个参数  正确 输出第二个参数 错误 输出第三个

max 返回最大的值

CONCAT 拼接字符串

JSON_CONTAINS 判断数组是否包含某个值

JSON_QUOTE 将字符串转换成json字符串 ""

    <!--  查找成员的聊天列表新   -->
    <select id="getUserConversation" parameterType="String" resultMap="ConversationResult">
        SELECT
            a.*,
            app_groupmodel.admin_list,
            app_groupmodel.`owner`,
            COALESCE (
                    app_groupmodel.`name`,
                    ea.`name`,
                    eb.`name`,
                    IF
                        ( ca.`userid` != '${userid}', ca.`name`, cb.`name` )
                ) AS `name`,
            COALESCE ( ea.follow_user, eb.follow_user ) AS follow_user,
            COALESCE ( ea.avatar, eb.avatar, ca.`avatar`, cb.`avatar` ) AS avatar
        FROM
            app_chatmodel AS a
                RIGHT JOIN ( SELECT max( seq ) AS id FROM app_chatmodel GROUP BY conver_id ) AS b ON a.seq = b.id
                LEFT JOIN app_extendcontactmodel AS ea ON a.`from` = ea.external_userid
                LEFT JOIN app_extendcontactmodel AS eb ON a.tolist = CONCAT( '["', eb.external_userid, '"]' )
                LEFT JOIN app_contactmodel AS ca ON a.`from` = ca.userid
                LEFT JOIN app_contactmodel AS cb ON a.tolist = CONCAT( '["', cb.userid, '"]' )
                LEFT JOIN app_groupmodel ON a.roomid = app_groupmodel.chat_id
        WHERE
        a.`from` = '${userid}'
        OR JSON_CONTAINS(
        a.`tolist`,
        JSON_QUOTE( '${userid}' ) )
    </select>