MyBatis 里使用双重 foreach 标签

接上一篇的需求，需要在 sql 里使用双重 foreach ，这里直接上代码

<select id="queryPeoples" parameterType="com.bean.People" result="string">
    select *
    from ( select id , GROUP_CONCAT(name) name from people
    <where>
        <if test="sex != null and sex != ''">
            AND sex = #{sex}
        </if>
    </where>
    group by id ) a
    <where>
        <if test="list != null and list.size > 0">
            <foreach item="item" collection="list" separator="and" index="">
                <if test="item != null and item.size > 0">
                    (
                    <foreach item="aa" collection="item" separator="or" index="">
                        FIND_IN_SET( #{aa.name}, a.name )
                    </foreach>
                    )
                </if>
            </foreach>
        </if>
        <if test="nonList != null and nonList.size > 0">
            and  a.id not in
            (
            select id from people where sex = #{sex} and name in
            <foreach item="item" collection="nonList" separator="," open="(" close=")" index="">
                #{ item.name }
            </foreach>
            )
        </if>
    </where>
</select>

执行的sql语句：

select *
from ( select id , GROUP_CONCAT(name) name from people where sex="woman" group by id ) a
where 
( FIND_IN_SET("aa", a.name) or FIND_IN_SET("bb", a.name) )
AND
( FIND_IN_SET("cc", a.name) or FIND_IN_SET("dd", a.name) )
AND
( FIND_IN_SET("ee", a.name) or FIND_IN_SET("ff", a.name) )
AND
( FIND_IN_SET("mm", a.name) or FIND_IN_SET("nn", a.name) )

括号里的or连接可以是一个或者多个or连接。

 

MYSQL中FIND_IN_SET()函数的使用

上一篇入口：Java List＜Object＞分组转成List＜List＜Object＞＞