66084

映射表链接查询技巧

通过 inner join 或者 where exists 来确保 主表的列满足一个条件

SELECT t.id, t.org_url, t.internal_url, t.publish_time, t.app_code, t.create_by, t.create_time, t.update_by, t.update_time FROM url_convert_record t <if test="keywordIds!=null and keywordIds.size()>0"> inner JOIN ( select distinct record_id from url_convert_record_keyword where keyword_id in <foreach collection="keywordIds" index="index" item="keywordId" open="(" separator="," close=")"> #{keywordId} </foreach> and is_valid = 1 )f ON f.record_id = t.id </if> <where> <if test="startPublishTime!=null"> and t.publish_time >= #{startPublishTime} </if> <if test="endPublishTime!=null"> AND t.publish_time &lt; #{endPublishTime} </if> <if test="appCode!=null"> AND t.app_code = #{appCode} </if> </where> ORDER BY t.update_time desc</select>或者 SELECTt.id,t.org_url,t.internal_url,t.publish_time,t.app_code,t.create_by,t.create_time,t.update_by,t.update_timeFROMurl_convert_record t<where> <if test="startPublishTime!=null"> and t.publish_time >= #{startPublishTime} </if> <if test="endPublishTime!=null"> AND t.publish_time &lt; #{endPublishTime} </if> <if test="appCode!=null"> AND t.app_code = #{appCode} </if> <if test="keywordIds!=null and keywordIds.size()>0"> AND EXISTS ( SELECT 1 FROM url_convert_record_keyword WHERE keyword_id in <foreach collection="keywordIds" index="index" item="keywordId" open="(" separator="," close=")"> #{keywordId} </foreach> AND is_valid = 1 AND record_id = t.id ) </if></where>ORDER BY t.update_time desc

来源:博客园

作者:zfzf1

链接:https://www.cnblogs.com/zfzf1/p/11425606.html

Recommend