こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

OracleSqlで『RORNUM』の使い方について

VB6でコーディングをしています
DBはOracle7.3です

SELECT A.One, A.Two, SUM(A.Three) Three, 
FROM A 
WHERE ( A.One = '001' AND A.Two = '3') 
GROUP BY A.One, A.Two
ORDER BY Three DESC 

上記のようにAテーブルの項目One,Two,Threeを抽出するSQLで
重複項目One,Twoでグループ化したThreeの合計値の内、上位25件を
取得したいのですが...

SELECT * 
FROM (
SELECT A.One, A.Two, SUM(A.Three) Three, 
FROM A 
WHERE ( A.One = '001' AND A.Two = '3') 
GROUP BY A.One, A.Two
ORDER BY Three DESC )
WHERE ROWNUM <= 25

上記のように記述するとORDER BY句でエラーになります
ORDER BY句をWHERE ROWNUMの下に記述するとSQLはとおりますが
条件が変わってしまいます。
抽出条件でマージ、降順ソートしたものに対して上位n件の取得をするには
どうすれば良いのでしょうか?

投稿日時 - 2001-02-21 11:18:37

QNo.43050

すぐに回答ほしいです

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

うーん、必ず1回のSELECT文で、25位まで取得しなくてはいけないんですか?
msystemさんもおっしゃっていますが、大抵はカーソルをつくって、25回ループさせて取得すると思います...
VB6ってことは、ミドルウェアはoo4oでしょうか?
oo4oならPL/SQLブロックをDBに投げて、ParamArray(だったっけ?)で値をもってこれるような気がしました。(これってOracle8の話なのかなぁ? 詳しくはoo4oのドキュメントを参照してください)
それか、ダイナセットをつくってループさせるっていうのが、多分いちばん単純で簡単です(こっちだとちょっと効率悪い?)。

# それじゃダメだから、質問しているんでしょうか...?

投稿日時 - 2001-02-23 23:08:33

お礼

プログラムが複数本あるため、このプログラムに関してだけPL/SQLを使用するのは一寸抵抗があります。
でも、貴重なお時間をさいて頂いて申し訳ありませんでした。

投稿日時 - 2001-02-26 09:09:03

ANo.3

一応できそうなSQL文がありますが、Oracleの動作保証がないため、VBのほうで上位25位をとることをお勧めします。

下にそのSQLを記述します。
まず、例で書いていただいたSQL文は必ず1行しか返ってこないのが気になりますが取りあえず無視して進めます。
まず、ソート用のテーブルを作成します。A.ThreeはNumber型だとします。
create table x (a number);
何でもいいので1行追加します。
insert into x values(0);
commit;
この準備ができたところで、以下のSQL文を投げます。
select d.one,d.two,d.three from
(select c.one,c.two,c.three,c.r,max(r) m from
(select b.one,b.two,b.three,rownum r from
(select a.one,a.two,sum(a.three) three from a
where (a.one='001' and a.two='3')
group by a.one,a.two) b,x
where b.three=x.a(+)) c
group by c.one,c.two,c.three,c.r) d
where r>=m-25
order by r desc;
多分、これで25位までの降順で結果が返ってくるはずです。

解説すると、一番内側のサブクエリが合計を出すクエリです。(質問のあったクエリ。ただし、この文だと1行しか返らない)
2番目のサブクエリで、外部結合させることにより並び替えをしています。それと同時に昇順の番号(r)をつけています。(ただし、これは副作用的に並び替えができているだけで、Oracleの保証はありません。これで昇順に並び代わります)
3番目のクエリで、昇順番号の最大値をとっています。
一番外側のSelect文で大きいほうから25位になるような絞込みと、降順の並び替えをしています。

もう少し、スマートな文もあるでしょうが、あとはご自分でがんばってください。

投稿日時 - 2001-02-23 10:51:13

お礼

とってもややこしいSQLをわざわざ考えて下さってありがとうございました(笑)
が、ソートテーブルを作成するやり方は一寸いただけないので、ここは諦めて全件抽出後にVBで25回ループする事にします。

投稿日時 - 2001-02-26 09:07:03

ANo.2

この問題はOracle8までだと、相当苦労します。
8i以降だとうまくいくのですが・・・

特に降順なので難しいと思います。ビューを使うのが手ではないでしょうか?

投稿日時 - 2001-02-22 08:29:26

お礼

御回答ありがとうございました
ビューを使うと言う事ですが、毎回抽出条件が変わるため使えません
何か効率の良い手はないものでしょうか?

投稿日時 - 2001-02-23 08:58:15

ANo.1

そうですね。複問合わせの中に order by は使えません。

●回答(?)例
SELECT A.One, A.Two, SUM(A.Three) Three
FROM A

の A.One の前に順序を発生させ ( i = 1,2,3,4,5,・… )
HAVING で i <= 25
でどうでしょう?

実際SQL文を動かしたわけではないので適当です。
すいません。。

投稿日時 - 2001-02-21 12:07:02

補足

sub queryにorder by句を記述できるのはoracle8以上なんですよね!
御回答いただいたHAVINGの例ですが、順序を発生させるとは?
実際にSQLのコーディングはどのようになるのでしょうか?
順序をとるとなると、やはりrounumを使わなければならないのではないでしょうか?

投稿日時 - 2001-02-21 13:05:05

あなたにオススメの質問