MySQL-代码-list转成多个like查询-like or list-查询存在并集的数据
<select id="selectNodeIdListByScene" resultType="com.safety51.edu.entity.user.UserIntermediate">
SELECT ui.id, ui.node_id_list nodeIdList FROM edu_user_intermediate ui inner join edu_train_implement i on i.user_intermediate_id = ui.id
WHERE ui.container_id = #{containerId} AND ui.scene = #{scene} AND ui.handling_status = 2
AND CHAR_LENGTH(node_id_list) > 1
<if test="parentIdList !=null and parentIdList.size() > 0">
AND <foreach collection="parentIdList" item="parentId" index="index" open="(" close=")" separator=" OR ">
ui.node_id_list like concat('%,',#{parentId},',%')
</foreach>
</if>
</select>
SELECT ui.id, ui.node_id_list nodeIdList FROM edu_user_intermediate ui inner join edu_train_implement i on i.user_intermediate_id = ui.id WHERE ui.scene = 3 AND ui.handling_status = 2 AND CHAR_LENGTH(ui.node_id_list) > 1 and (ui.node_id_list like "%,130,&" or ui.node_id_list like "%,134,%");
多个like查询，相当于查询存在并集的数据
SELECT ui.id, ui.node_id_list nodeIdList FROM edu_user_intermediate ui inner join edu_train_implement i on i.user_intermediate_id = ui.id WHERE ui.scene = 3 AND ui.handling_status = 2 AND CHAR_LENGTH(ui.node_id_list) > 1 and (node_id_list like "%,130,&" or node_id_list like "%,134,%");
+-------+---------------------------------------+
| id | nodeIdList |
+-------+---------------------------------------+
| 17453 | ,129,130,131,134,135,136,137,138,139, |
| 17455 | ,129,130,131,134,135,136,137,138,139, |
| 17456 | ,129,130,131,134,135,136,137,138,139, |
| 17457 | ,129,130,131,134,135,136,137,138,139, |
| 17513 | ,129,130,131,134,135,136,137,138,139, |
| 17514 | ,129,130,131,134,135,136,137,138,139, |
+-------+---------------------------------------+
6 rows in set (0.01 sec)
正文到此结束
