抽出結果にレコードNoを付与することは可能？

質問

mysql5.0.18です
よろしくお願いします

抽出結果にレコードNoを付与したいのです
実際には順位（同点は同順）としたいのですが
複雑になりそうなので
単純に、抽出結果に先頭から連番が振れればOKです

例えば
テーブル名:test_table
カラム：keys,data,value

keys　　data　　value
a001　　aaaa　　80
a002　　aaaa　　90
b001　　bbbb　　100
b002　　bbbb　　51

上記のテーブル内容から
抽出します

Select keys,data,value From
test_table Where keys Like '%002'
Order By value Desc,
data Asc

抽出結果
keys　　data　　value
a002　　aaaa　　90
b002　　bbbb　　51

上記抽出結果を
keys　　data　　value　　rec_no
a002　　aaaa　　90　　　　1
b002　　bbbb　　51　　　　2

のように
抽出結果に先頭から連番を振りたいのです

このようなことは実現できるのでしょうか？
temporaryは使用したくありません
（Dropのタイミングがよく分からないので・・・）
viewはOKです

理想は・・・
Select keys,data,value,rec_no(auto increment) From
test_table Where keys Like '%002'
Order By value Desc,
data Asc
のような事ができないのかな？と妄想していますTT

よろしくお願いします

回答No.2

chukenkenkou · ベストアンサー

#1回答者です。
MySQLのバージョンが5.0系とのことなので、ストアド・プロシジャを利用する方法もあります。

以下の関数は、変数@nを使用し、関数が呼ばれるたびに+1します。最初に呼ぶときには、変数@nを初期化（0を設定）しておきます。

●関数の定義
-- drop function user_rownum;
delimiter //
create function user_rownum()
returns smallint
begin
set @n=@n+1;
return @n;
end;
//
delimiter ;

●関数の利用
set @n=0;
select `keys`,`data`,`value`,user_rownum()
from `test_table`
where `keys` like '%002'
order by `value` desc,`data` asc;

お礼

デ、デ、出来ました！
DETERMINISTICのエラーが出て
どこにDETERMINISTICを入れるべきか悩みましたが
beginの前に
DETERMINISTICを入れることで解決しました

delimiter //
create function user_rownum()
returns smallint
DETERMINISTIC
begin
set @n=@n+1;
return @n;
end;
//

functionいいですね
勉強になりました
ありがとうございました！

回答No.1

chukenkenkou

SQL例です。

select x.`keys`,x.`data`,x.`value`,
(select count(*)+1
from test_table
where `keys`<x.`keys`
and `keys` like '%002'
) as rec_no
from test_table as x
where `keys` like '%002'
order by `value` desc,`data` asc;

お礼

お礼が遅れました
もうしわけございません

なるほど
select カラム名の、このカラムに
selectを入れ子できるとは知りませんでした
勉強になりました
ありがとうございました