連番のつけ方

ＳＱＬ初心者です。
よろしくお願いします。

顧客の売上順位を年度別に並べ連番を振りたいのですが
どのようにすれば良いのかわかりません。

＜テーブル例＞
年度　顧客CD　売上金額　順位
－－－－－－－－－－－－－－－－
2002　000001　　50000
2002　000002　　40000
2002　000003　　30000
2002　000004　　20000
2003　000001　　10000
2003　000002　　20000
2003　000003　　30000

上記のデータに順位を付与するＳＱＬ文を
書こうとしていますがなかなかうまくいきません。

＜処理結果例＞
年度　顧客CD　売上金額　順位
－－－－－－－－－－－－－－－－
2002　000001　　50000 　　1
2002　000002　　40000 　　2
2002　000003　　30000 　　3
2002　000004　　20000 　　4
2003　000001　　10000 　　3
2003　000002　　20000 　　2
2003　000003　　30000 　　1

という結果を求める場合、SQL文をどのように
書けばよいのでしょうか?
お教え下さいませ。

No.3 ベストアンサー 回答者： potedora 回答日時： 2004/07/02 18:09

#1のものです。

PL/SQLを作成してワークテーブルを作成した方が
はやそうですね。

一応サンプルです。
トランザクションは呼び出し形態がわからないので
記述していません。
insertをしているので
複数人が同時にSQLを発行する場合は排他の考慮が
必要です。

declare
cursor cr_tbl is
select distinct 年度 from 対象テーブル order by 年度 ;

rec_tbl cr_tbl%rowtype ;

begin

open cr_tbl ;

loop

fetch cr_tbl into rec_tbl ;
exit when cr_tbl%notfound ;

insert into ワークテーブル
select * from (
select 年度, 顧客ＣＤ, 売上金額, rownum　順位
from
(
select 年度, 顧客ＣＤ, 売上金額
from 対象テーブル
order by 売上金額
)
where 年度 = rec_tbl.年度
order by 年度, 顧客ＣＤ, 売上金額
)

end loop ;

close cr_tbl ;

end ;

この回答へのお礼

potedoraさん 本当にありがとうございます。m(__)m
私、初心者なので処理内容についてはなんとなくしかわかりませんが、サンプルを書いていただいたおかげで、見よう見まねで改造して実行したところ、うまくいきました!
このたびは本当にありがとうございました。

お礼日時：2004/07/03 09:43

No.4 回答者： kakusuke 回答日時： 2004/07/03 04:00

データベースファンクション

をつくって
ＳＱＬ文に組み込んだらダメ？？

No.2 回答者： kazuho_goo 回答日時： 2004/07/02 16:37

Oracle9iであれば

select 年度, 顧客CD, 売上金額, rank() over (PARTITION BY 年度 ORDER BY 売上金額) as 順位
from テーブル
でどうでしょう？

この回答への補足

ご回答ありがとうございます。
当方、残念ながら『Ｏｒａｃｌｅ８ｉ』なんです。
バージョンを書いていなくて申し訳ありません。

補足日時：2004/07/02 16:43

No.1 回答者： potedora 回答日時： 2004/07/02 16:12

上記の例でいえば、

select * from (
select 年度, 顧客ＣＤ, 売上金額, rownum　順位
from
(
select 年度, 顧客ＣＤ, 売上金額
from 対象テーブル
order by 売上金額
)
where 年度 = '2002'
order by 年度, 顧客ＣＤ, 売上金額
)
union
select * from (
select 年度, 顧客ＣＤ, 売上金額, rownum　順位
from
(
select 年度, 顧客ＣＤ, 売上金額
from 対象テーブル
order by 売上金額
)
where 年度 = '2003'
order by 年度, 顧客ＣＤ, 売上金額
)
order by 1,2

な感じになります。
通常は、ＵＮＩＯＮの単位になる部分をＶＩＥＷ等
にしてすっきりさせます。
また順位を取得するファンクションを作るなんて
方法もあります。またはワークテーブルも有りです。

この回答への補足

ご回答ありがとうございます。
やはり年度を指定しないとダメですか・・
というのは、実際のデータは１０年分ありまして、
さらに、顧客順位のｓｑｌができた後には
年度別、商品別の売上順も作らなければならないのです。
ですから、年度を固定値で入れるのではなく全てデータ
から生成したいと考えています。

他にもいろいろなやり方があるとの事なので
ご教授ください。よろしくお願いします。m(__)m

補足日時：2004/07/02 16:16