全外连接在开发中的运用实例！

青旅半醒 2024-02-19 17:40 21阅读 0赞

<select id="queryRole" parameterType="com.coship.web.uc.dto.RoleParam" resultType="com.coship.web.uc.model.Roles">
    		SELECT
    		t1.id,t1.NAME,t1.msg,t1.type,t1.creator_id,t1.LEVEL,t1.UPDATE_TIME AS updateTime,t2.user_id
    		FROM
    		t_role t1 LEFT OUTER JOIN t_user_role t2 ON t1.id=t2.ROLE_ID
    		WHERE 1 = 1
    		AND t1.creator_id = (
    		        SELECT id FROM t_user WHERE LOGIN_ID = #{loginId}
    		)
    		AND t2.USER_ID !=#{userId}
            <if test="id != null and id !=''">
                 and id = #{id}
            </if>
            <if test="name != null and name !=''">
                 and name like CONCAT('%', '${name}', '%')
            </if>
            <if test="msg != null and msg !=''">
                 and msg like CONCAT('%', '${msg}', '%')
            </if>
            <if test="type != null">
                 and type=#{type}
            </if>
            <if test="creator_id != null and creator_id !=''">
                 and creator_id=#{creator_id},
            </if>
            <if test="level != null and level !=''">
                 and level=#{level}
            </if>
            order by UPDATE_TIME desc
        </select>

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9saXViYW8x_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9saXViYW8x_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019022218244891.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW9saXViYW8x,size_16,color_FFFFFF,t_70