有些時候需要查詢給定ID的前一記錄后一記錄和ID對應(yīng)的記錄。比如一些新聞系統(tǒng)中，通過GET方法獲得文章ID需要顯示“前一篇文章”“后一篇文章” 和ID指定的文章。
下面是我用的一種查詢方法，可能效率，如果有更好的方法，請給我留言，不勝感激！
SELECT?art_id,?art_pdate
FROM?ecos_article
WHERE?art_id>75?limit?0,1
UNION
SELECT?art_id,?art_pdate
FROM?ecos_article
WHERE?art_id<=75?ORDER?BY?art_id?DESC?limit?0,3;
效果如下：
mysql> SELECT art_id, art_pdate FROM ecos_article limit 0,5;
+--------+------------+
| art_id | art_pdate? |
+--------+------------+
|???? 73 | 2005-12-01 |
|???? 74 | 2005-12-01 |
|???? 75 | 2005-12-01 |
|???? 76 | 2005-12-01 |
|???? 77 | 2005-12-01 |
+--------+------------+
mysql> SELECT art_id, art_pdate
??? -> FROM ecos_article
??? -> WHERE art_id>75 limit 0,1
??? -> UNION
??? -> SELECT art_id, art_pdate
??? -> FROM ecos_article
??? -> WHERE art_id<=75 ORDER BY art_id DESC limit 0,3;
+--------+------------+
| art_id | art_pdate? |
+--------+------------+
|???? 76 | 2005-12-01 |
|???? 75 | 2005-12-01 |
|???? 74 | 2005-12-01 |
+--------+------------+