用到sql变量,sql复用
<sql id="topSearch-common">AND ( CASE WHEN ps.online_time IS NOT NULL AND ps.offline_time IS NULLTHEN NOW() >= ps.online_timeWHEN ps.online_time IS NOT NULL AND ps.offline_time IS NOT NULLTHEN NOW() >= ps.online_time AND NOW() <= ps.offline_timeELSE ps.online_time IS NOT NULLEND )AND ( CASE WHEN @ps_type = 2 THEN ps.type=2 ELSE ps.type IN(0,1) END )AND pss.is_hide = 0 AND ps.is_delete = 0 AND ps.status = 0</sql><select id="topSearch" resultType="com.loyo.gm.model.entity.PostsSubSqlEntity">SET @likename = "%"#{query.keyword}"%" COLLATE utf8mb4_general_ci;SET @language_type = #{query.languageType};SET @ps_type = #{query.postsType};(SELECT pss.* FROM posts psINNER JOIN posts_sub pss ON ps.id = pss.post_idWHERE (pss.name LIKE @likename OR pss.key_words LIKE @likename)<include refid="topSearch-common"></include>UNIONSELECT pss.* FROM topic_tag ttINNER JOIN topic_tag_sub tts ON tt.id = tts.topic_tag_idINNER JOIN posts ps ON ps.tag_id = tts.topic_tag_idINNER JOIN posts_sub pss ON pss.post_id = ps.idWHERE tts.name LIKE @likename AND pss.language_type = @language_type AND tt.is_delete = 0<include refid="topSearch-common"></include>) ORDER BY id DESC</select>