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 回答日時： 2011/01/24 22:21

条件が異なるから普通に、二回

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;

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

お礼日時：2011/01/25 16:11

No.2 回答者： yambejp 回答日時： 2011/01/21 17:29

例示より長くなってしまいちょっと微妙なんですが

ちゃんとやるならこんな感じになりそうな気がします。
ちなみに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;

お礼日時：2011/01/21 17:43

No.1 回答者： nora1962 回答日時： 2011/01/21 15:20

例として提示されているのが「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 max(sequence) prev from user_table where group_id=1 and sequence<2) prev;

補足日時：2011/01/21 17:44

この回答へのお礼

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

正しくは下記になります。
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;

お礼日時：2011/01/21 17:41