根据id查询前一条后一条(共两条)
#{articleId}(articleId带花括号)为传过来的参数,即id
<!-- 根据文章id查询前一篇和后一篇文章 -->
<select id="selectPreAfterArticle" parameterType="long" resultMap="ArticleImageResultMap" >
SELECT * FROM article
WHERE article_id IN
(
SELECT
CASE
WHEN SIGN(article_id - #{articleId} ) > 0 THEN MIN(article_id)
WHEN SIGN(article_id - #{articleId} ) < 0 THEN MAX(article_id)
END
AS article_id
FROM article
WHERE article_id <> #{articleId}
GROUP BY SIGN(article_id - #{articleId} )
ORDER BY SIGN(article_id - #{articleId} )
)
ORDER BY article_id ASC
</select>
根据id查询前一条,本条和后一条(共三条)
SELECT * FROM article
WHERE article_id IN
(
SELECT
CASE
WHEN SIGN(article_id - #{articleid}) > 0 THEN MIN(article_id)
WHEN SIGN(article_id - #{articleid}) < 0 THEN MAX(article_id)
ELSE article_id
END
AS article_id
FROM article
GROUP BY SIGN(article_id - #{articleid})
ORDER BY SIGN(article_id - #{articleid})
)
ORDER BY article_id ASC;