[mysql]前後のデータ取得[メモ]

お久しぶりです

あまり使う機会がないけどmysqlで前後の行データの取得がしたい場合のsqlの書き方をメモしておきます。
例としてWordPressの投稿テーブル

SELECT 
@cur_id := ID as ID,
post_date,
post_title,
@prev_id := (SELECT ID FROM wp_posts WHERE post_type='post' AND post_status='publish' AND ID<@cur_id ORDER BY ID DESC LIMIT 1) as prev_ID,
(SELECT post_title FROM wp_posts WHERE ID=@prev_id ) as prev_title,
@next_id := (SELECT ID FROM wp_posts WHERE post_type='post' AND post_status='publish' AND ID>@cur_id ORDER BY ID ASC LIMIT 1) as next_ID,
(SELECT post_title FROM wp_posts WHERE ID=@next_id ) as next_title
FROM `wp_posts`
WHERE post_type='post' 
AND post_status='publish'
ORDER BY ID DESC
スポンサーリンク