MySQLでシーケンス番号の前後数を知りたい

質問

user_table
----------------------------
id | group_id | sequence| name |
----------------------------
1 | 1 | 1 | emi |
----------------------------
2 | 1 | 2 | shun |
----------------------------
3 | 2 | 1 | yumi |
----------------------------
4 | 1 | 5 | jo |
…
group_idごとにシーケンス番号（グループ内のユニーク番号）を振っています。
shun(group_id=1,sequence=2)のページでシーケンス番号の前後を取りたいのですがどうすればいいでしょうか？

すでに辞めたユーザもいるためシーケンス番号は必ず連続しているとは限らず、抜けている場合もあります。
例として、shun（group_id=1,sequence=2）の前後を取る場合はemiとjoのシーケンス番号を取得したいのです。

下記で出来ましたがサブクエリなしでもできそうなのとあまりきれいでないのでご教示お願いいたします。

select (select min(sequence) from t_files where album_id=1 and sequence>2) next,( select min(sequence) prev from t_files where album_id=1 and sequence<2) prev;

回答No.3

yuu_x · ベストアンサー

条件が異なるから普通に、二回
SELECT * FROM user_table WHERE group_id = 1 AND sequence > 2 ORDER BY sequence LIMIT 1;
SELECT * FROM user_table WHERE group_id = 1 AND sequence < 2 ORDER BY sequence DESC LIMIT 1;

どーしても一回で済ませたいなら、
SELECT
　MAX(CASE sequence < 2 THEN sequence ELSE NULL END) AS prev
　MIN(CASE sequence > 2 THEN sequence ELSE NULL END) AS next
FROM user_table
WHERE group_id = 1;

又は
SELECT
　*
FROM user_table
WHERE group_id = 1
　AND sequence >= (
　　SELECT sequence FORM user_table WHERE group_id = 1 AND sequence < 2
　　　ORDER BY sequence DESC LIMIT 1
　)
　AND sequence <> 2
LIMIT 2;

お礼

ありがとうございます。

ご教示いただいたものが少し間違っていたため下記のように修正し、実現しました。

SELECT
MAX(CASE when sequence < 2 THEN sequence ELSE NULL END) AS prev,
MIN(CASE when sequence > 2 THEN sequence ELSE NULL END) AS next
FROM user_table
WHERE group_id=1;

本当にありがとうございました！

回答No.2

yambejp

例示より長くなってしまいちょっと微妙なんですが
ちゃんとやるならこんな感じになりそうな気がします。
ちなみにSQLは効率を考えて2つにわけてあります

select @rank:=(
select count(*) from user_table as u2
where u1.group_id=u2.group_id
and u1.id>u2.id
) as rank
from user_table as u1
where group_id=1
and sequence=2
;
select (
select count(*) from user_table as u2
where u1.group_id=u2.group_id
and u1.id>u2.id
) as rank
,`u1`.* from user_table as u1
where group_id=1
having rank between @rank -1 and @rank +1

お礼

ありがとうございます。

ご教示いただいたものより、私がすでに書いたものほうが効率的な気がします。。

select (select min(sequence) from user_table
where group_id=1 and sequence>2) next,( select min(sequence) prev from user_table where group_id=1 and sequence<2) prev;

回答No.1

nora1962

例として提示されているのが「user_table」で、実際につくられたSQLのテーブルが「t_files 」、しかも「user_table」にはない「album_id」が出てきています。
「ページ」という言葉も出てますが、これがどういう意味なのかも分かりません。
ちょっと答えようがないです。

お礼

ご指摘いただきありがとうございます。
間違えておりました。

正しくは下記になります。
select (select min(sequence) from user_table
where group_id=1 and sequence>2) next,( select min(sequence) prev from user_table where group_id=1 and sequence<2) prev;

補足

お礼の文も間違えてました。。。

select (select min(sequence) from user_table
where group_id=1 and sequence>2) next,( select max(sequence) prev from user_table where group_id=1 and sequence<2) prev;