idを中心としたprev_idとnext_idの取り出し方（sql）

Question

php,mysqlを使用して、ある関数にidを引数として渡した時、その前後のデータも合わせて取り出したいと考えています。

注）以下は考え方の確認のため、表現しているので、実際のコードとは全然違います。

取り出したデータを'$id_data','$prev_id_data','$next_id_data'と表現すると

$id_data = SELECT * FROM hogehoge WHERE id = $id LIMIT 1

$next_id_data = SELECT * FROM hogehoge ORDER BY id ASC LIMIT $id+1,1

$prev_id_data = SELECT * FROM hogehoge ORDER BY id DESC LIMIT $id-1,1

のような形を思い浮かべたのですが、仕様を満たしません＾＾；

idが連番でないかつユニークな前提で、みなさんはどのようなやり方で取り出しているのでしょうか？

Answer 1

本来のSQLであれば「次に大きなID」をサブクエリで引く必要があるかもしれませんが、MySQLであればLIMITを使うことで回避できると思います。

-- idが次のもの
$next_id_data = SELECT * FROM hogehoge WHERE id > $id ORDER BY id ASC LIMIT 1

-- idが前のもの
$prev_id_data = SELECT * FROM hogehoge WHERE id < $id ORDER BY id DESC LIMIT 1

Comments

[退会済みユーザー]

LIMIT の offset に囚われていたので助かりました。
ありがとうございます。

Answer 2

あまりいいアイデアではないですが。
idがユニークである前提です。
取得したidでもとTBLに自己結合するなり、サブクエリでidを絞るなりすることで行データの取得もできるかと。
ただしindexが無いとだいぶ遅いと思います。

• ひとつ前のデータ

select max(id) from hogehoge where id < $id;

• ひとつ後のデータ

select min(id) from hogehoge where id > $id;

Comments

[退会済みユーザー]

ありがとうございます！この発想はありませんでした。面白いですね。

Answer 3

条件を追加して

# next_id
SELECT * FROM hogehoge WHERE id > $id ORDER BY id ASC LIMIT 1;

# prev_id
SELECT * FROM hogehoge WHERE id < $id ORDER BY id DESC LIMIT 1;

こんな感じは如何でしょう？

Comments

[tonarino210]

すみません、同じ解答がありましたね・・・orz

[退会済みユーザー]

LIMIT の offset に囚われていたので助かりました。
ありがとうございます。

[退会済みユーザー]

短時間での回答、ありがとうございます。
私もよくかぶりますｗ
今回はもう一方にBAさせていただきます。

Answer 4

あえて1クエリで取得するとしたら、以下のような感じでしょうか？

• IN句バージョン

SELECT * FROM hogehoge WHERE id IN (
    $id, 
    (SELECT id FROM hogehoge WHERE id < $id ORDER BY id DESC LIMIT 1), 
    (SELECT id FROM hogehoge WHERE id > $id ORDER BY id ASC  LIMIT 1)
) ORDER BY id;

• UNION ALL句バージョン

SELECT * FROM (
  (SELECT * FROM hogehoge WHERE id = $id)
    UNION ALL
  (SELECT * FROM hogehoge WHERE id < $id ORDER BY id DESC LIMIT 1)
    UNION ALL
  (SELECT * FROM hogehoge WHERE id > $id ORDER BY id ASC  LIMIT 1)
) AS tmp ORDER BY id;

• UNION ALL句バージョン（ちょっと最適化）

SELECT * FROM (
  (SELECT * FROM hogehoge WHERE id <= $id ORDER BY id DESC LIMIT 2)
    UNION ALL
  (SELECT * FROM hogehoge WHERE id >  $id ORDER BY id ASC  LIMIT 1)
) AS tmp ORDER BY id;

Comments

[退会済みユーザー]

ありがとうございます。これも面白いですね。
さくっと取れる。

IN句バージョンの「(SELECT * FROM」は「(SELECT id FROM」ですよね？
試してみましたが、一括でデータが取れて、便利でした。

[KiyoshiMotoki]

> IN句バージョンの「(SELECT * FROM」は「(SELECT id FROM」ですよね？

ご指摘ありがとうございます。
おっしゃる通りですね。

回答を修正させていただきました。

[退会済みユーザー]

ありがとうございます。勉強になりました。
うまく使わせていただきます。