77コメント32KB

SQL×SQL×SQL

■ このスレッドは過去ログ倉庫に格納されています

0001デフォルトの名無しさん

NGNG

0 ～4 のとき、'あ'
5 ～9 のとき、'い'
10～14 のとき、'う'
15～19 のとき、'え'
20～999のとき、'お'
が取得したいとき、
以下のようなテーブルを作りました。

TABLENAME FOO
COL1 COL2 COL3
0 あ A
5 い B
10 う C
15 え D
20 お E

で、COL2,COL3 を取得する際のSQLを

SELECT COL2,COL3
FROM FOO
WHERE COL1 =
(SELECT MAX(COL1) FROM FOO WHERE COL1<=6 GROUP BY COL1)
このとき COL2=い, COL3=B

これより、もっとシンプルな方法ってありますでそうか？

0002デフォルトの名無しさん

NGNG

>>1
TABLENAME FOO
COL1 COL2
0 あ
1 い
2 う
3 え
4 お
---
SELECT COL2
FROM FOO
WHERE COL1 = データ / 5;

テーブルから取得できないとき「お」とする。

0003デフォルトの名無しさん

NGNG

>>2
レスありがとうございます。
例として差が常に5となっていますが、ほんとはばらばらなんです。
まぎらわしくてすいませんでした。

0004名無しさん＠Ｅｍａｃｓ

NGNG

select col2, col3
from foo
where col1 < 6
order by col1 desc;

で cursor とか JDBC なら
Statement#setMaxRows() 使うってのはダメ？

SQLServer だと戻す行数指定できたと思う。

0005SQL鯖

NGNG

テーブルにＡというカラムとＢというカラムがあって、ソースに
Ａ＋Ｂの値を使用する場合、ＳＱＬでデータベースエンジンに計算させますか？
それとも、ソース内で計算させますか？

0006デフォルトの名無しさん

NGNG

>>5
どっちでもいいと思うが
計算はすべてDBに任せて、呼び出し側は結果をもらうだけにすると
見通しがよくなるだろうか。
そうなるといつもSQLプログラマに負担がかかるんだよね。

0007デフォルトの名無しさん

NGNG

ソースで計算していると、なにかあったとき（速度問題等）に割り切れないが
ＳＱＬが原因だと割り切れるよね（笑）

00081

NGNG

おはようございます。今週もがんばりましょう。

>>4
Statement#setMaxRowsってOracleのパッケージで使えますか？
もってる本には載ってないもので･･･。

0009デフォルトの名無しさん

NGNG

ORACLEならCOL1だけあればええやん。
SELECT DECODE(SIGN(COL1-5),-1,'あ',
　　　　　　　SIGN(COL1-10),-1,'い',
　　　　　　　SIGN(COL1-15),-1,'う',
　　　　　　　SIGN(COL1-20),-1,'え','お')
from foo

あとは、PL/SQLでストアドファンクションを作っちゃう
方法もあるよ。

00101

NGNG

言葉足らずですいません。
テーブルの中身は例であって
5づつ増えてもないし、あいうえおで決まってるわけでもないんです。
申し訳ないっす。

0011デフォルトの名無しさん

NGNG

最終手段
TABLENAME FOO
COL1 COL2
0 あ
1 あ
2 あ
3 あ
4 あ
5 い
6 い
7 い
8 い
9 い
10 う
11 う
12 う
13 う
:
:
999 お

0012デフォルトの名無しさん

NGNG

なにをやりたいのかさっぱりわからん。
集計関数を使ったデータの抽出って事？

00131

NGNG

>>11
確かに"最終"手段ですね(汗)

>>12
説明へたですいません。

例えば携帯電話なんかの長期利用割引を想像してください。
利用期間が何ヶ月からは何％割引します。
見たいなやつで、現在の利用期間から
現在の割引率、次回の割引率を求めたいんです。

う～む、説明がむずい･･･。

00141

NGNG

>>11
確かに"最終"手段ですね(汗)

>>12
説明へたですいません。

例えば携帯電話なんかの長期利用割引を想像してください。
利用期間が何ヶ月からは何％割引します。
見たいなやつで、現在の利用期間から
現在の割引率、次回の割引率を求めたいんです。

う～む、説明がむずい･･･。 ,212,sage ,2001/02/20(火) 15:31, なるほど、1のSQLだ。

あんまりいいの思いつかんけど、FROM句の中でサブクエリ書いた方が
WHERE句で書くよりちょっぴり早いかも。

役にたたないのでsage

0015デフォルトの名無しさん

NGNG

KeyFrom KeyTo Value
0 4 あ
5 9 い
：

SELECT Value FROM FOO WHERE ## >= KeyFrom AND ## <= KeyTO;

じゃぁだめかのぉ。
テーブルの形がもう決まっちゃってるなら、僕も１と同じことするなぁ。
サブクエリーでGROUP BYしてるのが謎だが

0016デフォルトの名無しさん

NGNG

レコード数が少なければMAX取るよりもソートして1件目のみ取得
の方がはやいかも

00179

NGNG

うわぁ。何書いてんだオレ。
詳しくは↓を見てもらうとして
ttp://www.mars.dti.ne.jp/~o-shin/new/kowaza/body530.html
IF COL1<X1 THEN Y1
ELSIF COL1<X2 THEN Y2
・
・
・
ELSIF COL1<Xn THEN Yn
ELSE Z
の時
SELECT DECODE(SIGN(COL1-X1),-1,Y1,
　　　　　DECODE(SIGN(COL1-X2),-1,Y2,
　　　　　　　　　・
　　　　　　　　　・
　　　　　　　　　・
　　　　　　DECODE(SIGN(COL1-Xn),-1,Yn,Z)
　　　　　　　　）
　　　　　　　）
FROM foo
と書けばOKって言いたかったんだけど・・・
言葉足らずですまぬ。

0018デフォルトの名無しさん

NGNG

そして、1の使っているDBが何なのか解らないまま
話は続いていくのであった・・・

0019デフォルトの名無しさん

NGNG

じゃあ終了

00201

NGNG

レス遅くなってすいません。
再開してもいいですか？（汗）

>>15
「サブクエリーでGROUP BYしてるのが謎」
ということはどういうことでしょうか？
ほかに効率のいい方法があったら指摘してもらえないでしょうか？

>>18
質問するときのお約束なのに忘れてましたね（謝）
環境は
NT4 Server
Oracle8 EE
です。

0021デフォルトの名無しさん

NGNG

15じゃないけど集計列しか取得しないならGroup Byを指定しなくても
いっしょって事じゃない？

いろいろレスついてるけど結果はどんな感じdesuka?

00224

NGNG

上で order by の方法を書いたけど
11, 15, 17 のどれかがいいと思うな。

携帯の割引率だったらそんなにテーブル大きくならないだろ?
だったら11の方法が結構いいと思う。

ちなみに Statement#setMaxRows は
Java の API だ。
JDBC 使わないんだったら関係ないよ。

0023デフォルトの名無しさん

NGNG

>>16
>>1ではないんだが質問よろしですか？
ソートして1件目のみ取得するってできるんですか？
どうやって取得すんのでしょう？
デービーはオラクルです。

0024デフォルトの名無しさん

NGNG

>>23
どういう状況の時、先頭レコードを取得できないのかが知りたい

0025デフォルトの名無しさん

NGNG

ソートしても複数レコードとってきちゃわないでしょうか？
「とってきた複数レコードの中の先頭レコード」ならわかるんだけど
ソートして1件目のみ取得できるというのができませんです。
hogehoge
col1
1
2
3
select col1 from hoge order by col1;
これだと3件取得しちゃいます。
MIN関数使わないでどうやれば1件だけ取得できますか？

0026デフォルトの名無しさん

NGNG

>>25
OracleにはPostgreSQLの言うところのLimitってないのかな。
…とりあえずレコード全部持っちゃって、１件目の値だけ
利用するでもいいと思うんですが。

0027デフォルトの名無しさん

NGNG

むううう、PostgreSQLを知らんのでLimitも？です。
where order = 1
とかできたらいいのになぁ･･･とかおもた。

0028デフォルトの名無しさん

NGNG

Oracleならrownum。
where rownum <= 10とか。

0029デフォルトの名無しさん

NGNG

おぉ、なんだそりゃ、すごい！
とおもてマニュアル見たら、
「Oracle は検索した各行に ROWNUM 値を割り当ててから、
ORDER BY 句に従ってソートします。」
という記述が･･･。
これ逆だったらすごく使えると思うでしょう。
とても残念です。なんとかならないですねえ。

0030デフォルトの名無しさん

NGNG

環境ないけど、fromにソートしたサブクエリ書いて、
外側でrownumで取れない？？

003128

NGNG

>>30
出来る。
ちょい時間がかかる and バージョンいくつから使えるかしらんけど。

select * from (select * from table_name order by col1 desc)
where rownum < 10

みたいにね。8iからかな？8.0.5くらいからかもしれん。

003228

NGNG

Pro*Cだと配列フェッチすれば似たようなことが出来る。

0033デフォルトの名無しさん

NGNG

おぉ、すばらC！
福問合せを使うわけですな！
では、>>1 に書いてあるSQLも
ROWNUMでいけるわけですね…ふむふむ。

0034デフォルトの名無しさん

NGNG

あの、その前に
>>16 さんが言ってた「こっちのが速い」を検証した方がいいんじゃないかと。
>>33 さんの環境でな。

0035デフォルトの名無しさん

NGNG

Oracleならこんなのも。

あらかじめソートしたいカラムで索引を作成して、
SELECT /*+INDEX_DESC (テーブル名索引名) */
カラム1,カラム2...
FROM テーブル名
WHERE 条件.... and
ROWNUM = 1;

索引が使われること確認しとかないとダメだけど。

003633

NGNG

>>34
あ、いや、仕事で書くわけでなくて
このスレッド見て、ちょと疑問におもただけなんで。
でも、ROWNUMを知ることができてよかたです。

>>35
おぉ、ヒントというやつですな。
まだつかたことないんでいまから調べて見ます。
ちなみに
> 索引が使われること確認しとかないとダメだけど。
これはインデックスが張られてればいいという意味でそうか？
つまりKEY項目であればいいということでそうか？

0037デフォルトの名無しさん

NGNG

>>24
速度は>>11が一番速いです

>>36
>これはインデックスが張られてればいいという意味でそうか？
トレースとって下さい

003835

NGNG

>これはインデックスが張られてればいいという意味でそうか？
>つまりKEY項目であればいいということでそうか？
実行計画によってインデックス使う時と使わない時があります。
使われないとデタラメな結果になるんで、37さんの言うとおりトレース取って
確認しとかないとってぇことです。KEYである必要はないです。

で、RULEヒント使えばインデックス必ず使うんだっけ？
あれ？二重にヒント使えたっけ？
ごめんなさい今環境ないもんで・・

0039デフォルトの名無しさん

NGNG

でも、基本はルールベースより、コストベースでやったほうが
いいんだよな。

0040デフォルトの名無しさん

NGNG

なるほど…。勉強になるわ。

0041デフォルトの名無しさん

NGNG

>select * from (select * from table_name order by col1 desc)
>where rownum < 10
>
>みたいにね。8iからかな？8.0.5くらいからかもしれん。
8.0.5は不可みたいだ（今試してみた）
福参照にORDER BYは使えないみたい。でも、GROUP BYは使えるから
単一行ならできるかも。

0042デフォルトの名無しさん

NGNG

bitmapインデックスを使いながらトレースを取ると落ちることあるにょ
それはバグなので、最新のパッチを適用すれば直るにょ

0043仕様書無しさん

NGNG

　　　　　,一-、
　　　　 /￣　l |　　　／￣￣￣￣￣￣￣￣￣￣
　　　　■■-っ　＜　んなーこたーない
　　　　´∀｀/ 　　　＼＿＿＿＿＿＿＿＿＿＿
　　＿_／|Ｙ/＼.
　Ё|＿_　| /　　|
　　　　| У..　　|
.

0044仕様書無しさん

NGNG

　　　　　,一-、
　　　　 /￣　l |　　　／￣￣￣￣￣￣￣￣￣￣
　　　　■■-っ　＜　んなーこたーない
　　　　´∀｀/ 　　　＼＿＿＿＿＿＿＿＿＿＿
　　＿_／|Ｙ/＼.
　Ё|＿_　| /　　|
　　　　| У..　　|

0045休日出勤HELPさん

NGNG

全く同一の行を片方だけ削除すんのってどうやんの～？
あ、Oracle7です。

0046休日出勤明けさん

NGNG

みんな意地悪だ。わ～ん
あ、rowidとか言うのをうまい事アレしてどうにかなりました。

0047デフォルトの名無しさん

NGNG

>>46
distinctでどうにかする、っつーのじゃなくて？

0048デフォルトの名無しさん

NGNG

作成済みVIEWの定義をSQLPLUSで参照する方法ありますか？
DESCではカラムの情報がわかるけど、
SELECT文の情報が知りたい。

0049デフォルトの名無しさん

NGNG

>>48
user_viewsだとかdba_viewだとか

0050デフォルトの名無しさん

NGNG

>>49
48です。
それってかなり長いSQL文
（テキストファイルにして10KBくらい）
でも表示できますか？

以前、途中で切れたような覚えがあるのですが。
ちなみにORACLE7.3.4です。
途中で切れない方法があったら教えてください。

0051Relational Database?

NGNG

http://www.borland.co.jp/tips/delphi/dh003/index.html

↑こんなバカも真面目にサポートせにゃならんとは・・・しんどい仕事だ。

0052デフォルトの名無しさん

NGNG

>>51
SQLもわからんのにテーブル設計している奴もいるしな(嘆息
そいつが書いた奴はネタに使ってこっちで作り直し。
御里(dBase)へ帰れよと言いたい。＞今のクライアント

0053デフォルトの名無しさん

NGNG

オラクルのSQLに詳しい人に聞きたいんだけど…
ビット演算の結果をSQLに組み込むことってできる？

お客さんの今のデータベースにチェックボックスの内容が入ってて、
例えば1,2,4,8,16,256と入ってたら287とか入ってるフィールドがあって、
それに対してダイレクトで16はONかOFFかってのを取り出せるようなSQLが
書きたいんですよ。

一旦VBとかに落としてからなら簡単なんだけどもデータ件数があまりに膨大
なもんでなんとかselect文の段階で絞り込みたいんだけど…
誰か助けてくださいませんか？

0054デフォルトの名無しさん

NGNG

今、SQL Server 7.0を使っているのですが、

氏名　　国語　　算数　　理科　　社会
-----------------------------------
小渕 80 100 70 65
小泉 95 85 85 90
森　 25 30 40 35

というようなテーブルから、個人別に一番高い得点を抽出し、

氏名　　最高得点
-----------------
小渕　　100
小泉 95
森 40

というように別のテーブルに格納したいのですが、同一レコード内で
カラム値の大小を比較、最大値を抽出する方法で悩んでいます。
どなたか教えて頂けないでしょうか。お願いします。

0055デフォルトの名無しさん

NGNG

>>54
union使う

0056デフォルトの名無しさん

NGNG

>>53
ビット演算の結果をSQL書き込むことが目的じゃなくて、
「チェックボックスの内容」を書き込みたいんじゃないんですか？

だとしたら、ビットに投射するよりも、すなおに正規化したほうが
後に禍根を残しませんよ。

0057デフォルトの名無しさん

NGNG

>>56
あー、そうかもしれませんね。
とりあえず小さなファンクションでも作るかな。
かけてからMODして引いた方が速そうだ。

0058デフォルトの名無しさん

NGNG

なんとかできたか…
こんなの効率悪いって方いらっしゃったらツッコミよろ。

create or replace function GetOptValue
(optnum in Number, optmark in number)
Return Number
is
rtn Number(1);
tmpOpt Number;
MOPT Number;
begin
tmpOpt := optnum * 2;
select mod(optmark,tmpOpt) into MOPT from dual;

if (MOPT / optnum) >= 1
then
return 0;
else
return 1;
end if;

end;
/

0059デフォルトの名無しさん

NGNG

>>54
ORACLEだったら
SELECT 氏名,GREATEST(国語,算数,理科,社会) FROM HOGE
で一発なのにねぇ～
SQL Serverは分からんわ

0060デフォルトの名無しさん

NGNG

>>57
ビットそれぞれに意味をもたせるのは、第一正規系に反しています。
もしスキーマの変更が出来るなら、真剣に考えてみてください。

万一性能上の問題が出た場合（レコード数が多いというので、出そう
なんですが）ビットの論理和しかないと、どうしようもなくなる可能性が
あります。

0061デフォルトの名無しさん

NGNG

ファンクションをSELECTの選択リストに入れたり、WHERE句に入れたり
すると、とんでもなく時間がかかる場合があります。

EEを使っていてファンクションインデックスが使える場合は、かなり
性能改善できる場合もありますが、それも限度があります。

0062デフォルトの名無しさん

NGNG

>>60,61
うーんやっぱりちょっと遅いかなぁ？
でもファンクションをSelectに入れるってのは避けれらない
ことだとは思うんですが。標準装備の奴だって結局は関数だし。

まーまずBitAndぐらい装備しといてくれたらいいのに。＞オラクル
スキーマの変更は考えた方がいいかもしれませんね。
まぁもとよりそのDB自体が最初からそうなってあったんだとすると
移行は相当に大変そうですが…

0063デフォルトの名無しさん

NGNG

>>62
>でもファンクションをSelectに入れるってのは避けれらない
>ことだとは思うんですが。標準装備の奴だって結局は関数だし。

標準装備の奴は、何故だか速いんです。
本格的に移行するまえに、是非、本番と同程度のデータ量で
ある程度のパフォーマンス評価をすることをお勧めします。

機能差分をストアードファンクションで、とか、複雑なビューで、
という方針で行くと、失敗する可能性が高いです。

地獄のパフォーマンスチューニングが待ってます（泣き）

0064デフォルトの名無しさん

NGNG

あ、もちろん、想定するデータ量で、ストアードファンクションを
いくつか使ったDMLを書いても、十分なパフォーマンスが期待できる、
と見通しが立ったら、そのまま移行を続行しても良いと思います。

でも、大抵は「第一正規形に違反しているスキーマ」に対する
プログラミングは複雑なものになり、拡張性も乏しいものに
なってしまうんですが･･･。

0065デフォルトの名無しさん

NGNG

ちなみに、隣のチームで似たようなことやってて、
そのシステムでは、ある個人がMAX1000個くらいの
ON/OFF情報を持ってて（ONは普通数個～数十個）
それをビットの論理和で持ってしまい（過去の実装
がそうなっていたので）、とんでもないコトになって
います。

私も、チューニングに駆り出されましたが、アドホックな
問い合わせも出来ないようなスキーまで、大変苦労
しました。

0066デフォルトの名無しさん

NGNG

なるほど。よく解りました。
一応今回はビット配列も大した数じゃないので
テストをしてみてもそんなに遅すぎるというほど
負荷は感じられなかったので、無理矢理ファンクションで
乗り切ろうと思います。
ま、自分で最初からデータベースが作れたんだったら
こんなことにはならなかったんですが…。
なんせエライ古いものの機能追加なんで。

まぁこれを改善したデータベースもあるんですが…
スラッシュ区切りの固定長の値というもしかしたら
もっと遅いかもしれない奴です。（T_T

こっちはLikeで'%001%'が入ってるかどうか？と探すので
書きやすいのは書きやすいんだけど…
どうせなら設計からやらせてくれー。

0067デフォルトの名無しさん

NGNG

>スラッシュ区切りの固定長の値というもしかしたら
>もっと遅いかもしれない奴です。（T_T

固定長の方が速いと思われ

0068oracle七日目

NGNG

Oracleで延々つまってます。３つのテーブルがあるとします。

TABLE_B

A_ID , NAME
----------------
0 AAAA
1 BBBB

TABLE_B

B_ID , A_ID , SCORE , C_ID
------------------------------
0 0 0 100
1 0 8 101
2 1 8 101
2 1 8 103

TABLE_C

C_ID , USER_NAME
-----------------
100 A
101 B
102 C
103 D
104 E

があるとします。。それで、

C.C_ID B.SCORE A.NAME
-----------------------------
100 0 AAAA
101 8 AAAA
102 NULL AAAA
103 NULL AAAA
104 NULL AAAA

100 NULL BBBB
101 8 BBBB
102 NULL BBBB
103 8 BBBB
104 NULL BBBB

と、いうような結果を出したいのです。

SELECT TABLE_A.A_ID , TABLE_B.SCORE , TABLE_C.USER_ID
FROM TABLE_A , TABLE_B , (SELECT C_ID FROM TABLE_C WHERE C_ID >= 100) TABLE_C
WHERE TABLE_A.A_ID (+) = TABLE_B.A_ID AND TABLE_C.C_ID = TABLE_B.C_ID(+)
ORDER BY TABLE_A.A_ID

こんな感じのを書きましたが、思うような結果になってくれません。
どこがイケナイでしょうか？？（Table_Cは、C_IDが100以上が条件になるので、
上記のような書き方にしてます。

長くてすいません。どなたか助けて下さい。これができないと、明日も出社です。とほほ。。

0069>>68

NGNG

これは難しい。おれじゃむりだ。

0070デフォルトの名無しさん

NGNG

>>68
AとCの積にBがぶらさがるってイメージ？？

試してないけど
TABLE_A.A_ID (+) = TABLE_B.A_ID
これがいらないかも。

007170

NGNG

やっぱおかしいね
これでどう？

SELECT
D.C_ID,
B
D.A_NAME
FROM
(SELECT
A.ID A_ID,
A.NAME A_NAME,
C.ID C_ID,
C.USER_NAME C_USER_NAME
FROM
A,C) D,
B
WHERE
D.C_ID = B.C_ID(+)
AND D.A_ID = B.A_ID(+)

007268

NGNG

>>70
ありがとうございます。

じつは明日（っていうか今日…）、出社するハメになってしまったので、
会社で試してみます。
結果も報告します。ありがとうございました。

0073oracle七日目

NGNG

>>70
うまくいきました！やりたいことが実現できてよかったです。
ありがとう。本当にありがとうございます。

007470

NGNG

うまくいったのならいいんだけど
元のSQL
>TABLE_A.A_ID (+) = TABLE_B.A_ID
ここを
TABLE_A.A_ID = TABLE_B.A_ID (+)
してもよかったかも。

どういうIndexのはり方してるかわかんないけど
こっちの方が速いかも。

0075初心者です

NGNG

オラクル？
今、CONPUTE句でフィールド別に集計をかけてるんですが、
別マスタのデータを同時に落とせないんでしょうか？

SELECT Hcode = マスタＡ.Hcode,Hname = マスタＡ.Hname,Tcode = マスタＢ.Tcode,Tname = マスタＢ.Tname,
マスタＤ.型式,マスタＤ.日付,sum(マスタＤ.数量) CONPUTE数量,sum(マスタＤ.粗利) CONPUTE粗利,sum(マスタＤ.金額) CONPUTE金額

FROM マスタＢ
INNER JOIN マスタＤ ON マスタＢ.Tcode = マスタＤ.Tcode
INNER JOIN マスタＡ ON マスタＢ.Hcode = マスタＡ.Hcode

WHERE マスタＡ.Hcode = 8 AND
マスタＤ.Tcode = 35 AND
マスタＤ.日付 >= '2001/06/01' AND
マスタＤ.日付 <= '2001/06/30' AND

GROUP BY マスタＡ.Hcode,マスタＡ.Hname,マスタＢ.Tcode,マスタＢ.Tname,
マスタＤ.型式,マスタＤ.日付

こんな感じで集計したいのですが、他のマスタを同時に使えないのでしょうか？
ものすごく分かりにくく書いてしまいましたが、どなたか分かる方いらっしゃらない
でしょうか？

0076デフォルトの名無しさん

NGNG

A,B,Cというカラムがあったとして、

SELECT A,B,C FORM TABLE;
みたいなSQL文のABCをまとめてひとつの値として
プログラムに返すことできますか？

0077デフォルトの名無しさん

NGNG

SELECT A || B || C FROM TABLE;
でよかったんじゃないかなぁ・・・夏ばて

■ このスレッドは過去ログ倉庫に格納されています