チョイスタのOracleをしていての疑問や知らなかった機能とかのメモ
時々答えがあったりなかったり。。
■ MAX関数の疑問
① 同じ値が複数列にある場合、どの値を返すのか?
名前 | 得点
--------+---------
Aさん | 100
Bさん | 90
Cさん | 100
Dさん | 85
select 名前 , MAX(得点) from 成績表 group by 名前
→?
MAXは複数の値を返す?
② NULLをMAX関数はどのように扱う?(①の疑問とややかぶっているが)
名前 | 得点
--------+---------
Aさん | NULL
Bさん | NULL
Cさん | NULL
Dさん | NULL
select 名前 , MAX(得点) from 成績表 group by 名前
→?
■ 副問い合わせ
① 副問合せが NULL を戻すと主問合せは1行も結果を戻さない
→ nvlとかで置き換えをすると大丈夫なのでは?
② INとNOT INの思わぬ違い
"IN" を用いた場合、副問合せが返す複数の値のうち、一つだけがNULLでも主問合せは結果を返す
"NOT IN" を用いた場合、副問合せが返す複数の値のうち、一つでもNULLがあれば主問合せは結果を返さない
■ NULLIF と NVL2
NULLIF( 式1, 式2)は式1と式2を比較し、等しい場合はNULLを戻す
NVL2(式, 値1, 値2) は、式の値がNULL以外 の場合、値1 を戻し、NULL の場合、値2を戻す
■ 切捨てとか
CEIL(n)はn以上の最も小さい整数を戻します。
FLOOR(n)はn以下の最も大きい整数を戻します。
MOD(m,n)はmをnで割ったあまりを戻します。
POWER(m,n)はmをn乗した値を戻します
SQRT(n)はnの平方根を戻します。
■ 速度向上?
order byをサブクエリと両方でする。早い、ような?
select a , b , c
from
(select a , b , c from DD order by a , b , c)
order by a , b , c
■ distinct と order by
一緒にするとエラーが出る(ORA-01791: SELECT式が無効です)
例)
select distinct A_1 , A_2
from TblA left outer join TblC on (A_2 = TblC.Code)
order by A_1 , C_order , A_2
selectの中に、C_orderが無いと上記エラー
なんで?
distinctをする場合、order byの項目はselectに含める必要がある、と明記されている
http://msdn.microsoft.com/ja-jp/library/Cc350739
どうも、order byとdistinctは両方とも並べ替えをするらしい。
両方あると、並べ替えが競合するのでエラーになる・・・というDBもある。
http://support.microsoft.com/kb/96895/ja
よくわからんが、同じ問題で悩んでいる人は多い。
原理がわからなくてモヤっとするが、それほど困る問題でもない。例の場合は、C_orderをselectに入れとけばいい。
(selectに含められないなら仕方ないが)
この辺が仕様の答えになるの・・・かもしれない?
http://oraclesqlpuzzle.hp.infoseek.co.jp/10-211.html
http://oraclesqlpuzzle.hp.infoseek.co.jp/1-6.html
ちなみに、この問題が起こったSQLは、最終的にこんな感じにした。
(※ 欲しい答えを求めた結果であって、問題解決そのものではないので注意)
select A_1 , A_2
from(
select ditinct A_1 , 0 as A_2
from TblA
union all
select ditinct 0 as A_1 , A_2
from TblA
) left outer join TblC on (TblA_2 = TblC.Code)
order by A_1 , C_order , A_2
たいそうなSQLになってしまったなぁ・・・。
■ union all と order byを併用するとエラーになる
なるらしい。
回避するには、全部のunion allの後に、order byをする。
参考
http://www.atmarkit.co.jp/fnetwork/rensai/sql09/sql1.html
なんで?
■ 今さらオラクルSQLの基礎
システム日付を取得する
select SYSDATE from dual;
時々答えがあったりなかったり。。
■ MAX関数の疑問
① 同じ値が複数列にある場合、どの値を返すのか?
名前 | 得点
--------+---------
Aさん | 100
Bさん | 90
Cさん | 100
Dさん | 85
select 名前 , MAX(得点) from 成績表 group by 名前
→?
MAXは複数の値を返す?
② NULLをMAX関数はどのように扱う?(①の疑問とややかぶっているが)
名前 | 得点
--------+---------
Aさん | NULL
Bさん | NULL
Cさん | NULL
Dさん | NULL
select 名前 , MAX(得点) from 成績表 group by 名前
→?
■ 副問い合わせ
① 副問合せが NULL を戻すと主問合せは1行も結果を戻さない
→ nvlとかで置き換えをすると大丈夫なのでは?
② INとNOT INの思わぬ違い
"IN" を用いた場合、副問合せが返す複数の値のうち、一つだけがNULLでも主問合せは結果を返す
"NOT IN" を用いた場合、副問合せが返す複数の値のうち、一つでもNULLがあれば主問合せは結果を返さない
■ NULLIF と NVL2
NULLIF( 式1, 式2)は式1と式2を比較し、等しい場合はNULLを戻す
NVL2(式, 値1, 値2) は、式の値がNULL以外 の場合、値1 を戻し、NULL の場合、値2を戻す
■ 切捨てとか
CEIL(n)はn以上の最も小さい整数を戻します。
FLOOR(n)はn以下の最も大きい整数を戻します。
MOD(m,n)はmをnで割ったあまりを戻します。
POWER(m,n)はmをn乗した値を戻します
SQRT(n)はnの平方根を戻します。
■ 速度向上?
order byをサブクエリと両方でする。早い、ような?
select a , b , c
from
(select a , b , c from DD order by a , b , c)
order by a , b , c
■ distinct と order by
一緒にするとエラーが出る(ORA-01791: SELECT式が無効です)
例)
select distinct A_1 , A_2
from TblA left outer join TblC on (A_2 = TblC.Code)
order by A_1 , C_order , A_2
selectの中に、C_orderが無いと上記エラー
なんで?
distinctをする場合、order byの項目はselectに含める必要がある、と明記されている
http://msdn.microsoft.com/ja-jp/library/Cc350739
どうも、order byとdistinctは両方とも並べ替えをするらしい。
両方あると、並べ替えが競合するのでエラーになる・・・というDBもある。
http://support.microsoft.com/kb/96895/ja
よくわからんが、同じ問題で悩んでいる人は多い。
原理がわからなくてモヤっとするが、それほど困る問題でもない。例の場合は、C_orderをselectに入れとけばいい。
(selectに含められないなら仕方ないが)
この辺が仕様の答えになるの・・・かもしれない?
http://oraclesqlpuzzle.hp.infoseek.co.jp/10-211.html
http://oraclesqlpuzzle.hp.infoseek.co.jp/1-6.html
ちなみに、この問題が起こったSQLは、最終的にこんな感じにした。
(※ 欲しい答えを求めた結果であって、問題解決そのものではないので注意)
select A_1 , A_2
from(
select ditinct A_1 , 0 as A_2
from TblA
union all
select ditinct 0 as A_1 , A_2
from TblA
) left outer join TblC on (TblA_2 = TblC.Code)
order by A_1 , C_order , A_2
たいそうなSQLになってしまったなぁ・・・。
■ union all と order byを併用するとエラーになる
なるらしい。
回避するには、全部のunion allの後に、order byをする。
参考
http://www.atmarkit.co.jp/fnetwork/rensai/sql09/sql1.html
なんで?
■ 今さらオラクルSQLの基礎
システム日付を取得する
select SYSDATE from dual;