Works > 逆引きSQL比較 > ORACLE > SQL
ORACLE
[ SQL ]
[ データベースの情報 ]
[ 手続き型言語 ]
[ その他 ]
■SQL
この項で使用するサンプルテーブルについて ▲TOP
日付型データをフォーマットして表示するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
--YYYY-MM-DD
select to_char(sysdate,'YYYY-MM-DD') "NOW" from dual;
--YYYY-MM-DD HH:MM:SS
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "NOW" from dual;
--YYYYMM
select to_char(sysdate,'YYYYMM') "NOW" from dual;
日付の演算あれこれ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
--1日後の日を求める（1日前ならマイナスにすればよい）
select sysdate + 1 from dual;
--1月後の日を求める（1月前ならマイナスにすればよい）
select add_months(sysdate,1) from dual;
--日付間の日数（最も古い売上.販売日から何日経ったか）
select sysdate - min(saledt) from ksale;
--日付間の月数（最も古い売上.販売日から何ヶ月経ったか）
select months_between(sysdate,min(saledt)) from ksale;
--日付間の年数（最も古い売上.販売日から何年経ったか ※関数はないので月数÷12）
select months_between(sysdate,min(saledt))/12 from ksale;
--日付の月末日を求める
select last_day(sysdate) from dual;
INSERT時にキーを自動的に連番で振るには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
サンプルの販売実績テーブル（ksale）の主キー saleno は自動的に連番が振られるようになっています。
各RDBMSデータ作成用のスクリプトを参考にしてください。
--順序（SEQUENCE）オブジェクトを作成しておく必要があります。
create SEQUENCE hoge_seq start with 1 increment by 1 maxvalue 99999 cycle nocache;
--INSERT時には以下の様に nextval を使って、明示的に順序をインクリメントする必要があります。
insert into hoge(col) values (hoge_seq.nextval);
-- ※ つまり、オラクルには、他のデータベースにあるようなオートナンバー型はないのです。
同一テーブル内の別の行にある情報を結合して検索するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
自己結合を使用します。
スタッフ名（社員.社員名）の横に、所属支店の支店長の名前（これも社員.社員名）を表示します。
select e1.empnm "スタッフ名" , e2.empnm "支店長名"
from kemp e1, kemp e2
where e1.deptno=e2.deptno
and e1.job = 'スタッフ'
and e2.job='支店長';
--9i以降のANSI構文
select e1.empnm "スタッフ名" , e2.empnm "支店長名"
from kemp e1 inner join kemp e2
on e1.deptno=e2.deptno
where e1.job = 'スタッフ'
and e2.job='支店長';
結合条件に合致しなくてもすべての行を表示させるには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
外部結合を使用します。
始めに整理しておくと、ここで書いている外部結合の構文には
left outer join ：ANSI（ORACLE 9i以降,MSSQL,SSA,Jet）
外部結合演算子(+)：ORACLE 8i以下
外部結合演算子 * ：MSSQL,SSA（SYBASE SQL Server から引き継がれている構文）
の3種類があります。
個人的には SYBASE の方式が最も理解しやすいと思います。
『*（ワイルドカード）のある側はすべて』
という風に覚えられるからです。ORACLE はその逆と覚えればよいでしょう。
以下は、社員単位の販売実績を表示します（ただし販売実績のないスタッフも名前だけは表示する）。
select e.empnm "スタッフ名", s.saledt "販売日" , s.qty "販売数量"
from kemp e , ksale s
where e.empno = s.empno (+)
and e.job = 'スタッフ'
order by e.empno,s.saledt;
-- ※売上テーブル側に条件を追加する時の留意点
-- 『販売数量が10の販売実績のみ表示（ただし条件に該当しないスタッフも名前だけは表示する）』
-- という場合の例（実際はこんな検索はしないでしょうが、まあ例ですから）。
-- ここで注意して欲しいのが、結合条件ではない QTY にも外部結合演算子(+) を付けている点です。
-- これがないと正しい結果は取得できません。
-- SYBASE構文（MSSQL,SSA）では、外部結合演算子を付ける辺がORACLEと逆なので必要ありません。
select e.empnm "スタッフ名", s.saledt "販売日" , s.qty "販売数量"
from kemp e , ksale s
where e.empno = s.empno (+)
and s.qty (+) = 10
and e.job = 'スタッフ'
order by e.empno,s.saledt;
--9i以降のANSI構文
select e.empnm "スタッフ名", s.saledt "販売日" , s.qty "販売数量"
from kemp e left outer join ksale s
on e.empno = s.empno
where e.job = 'スタッフ'
order by e.empno,s.saledt;
SELECT文の中で条件分岐するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
販売数量合計が200以上の人は成績に「優」、100以上の人は「良」、それ以外は「可」と表示します。
--ORACLE8i 以降では CASE文が使用できますが、8.0以前のバージョンでは、DECODE , GREATEST , LEAST
--を組み合わせて分岐制御します。ELSEを使用する場合は DECODE を入れ子にします。
select e.empnm , sum(s.qty) "売上",
decode(greatest(sum(s.qty),99),99,'可',decode(greatest(sum(s.qty),199),199,'良','優')) "成績"
from ksale s, kemp e
where s.empno = e.empno
group by e.empnm;
クロス集計の方法は？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
ORACLE8以降及びMSSQL6.5以降では、クロス集計用にANSIのROLLUP,CUBE が使用できますが、エンドユーザーが求めているレイアウトとは違うような気がします（この認識って間違い？）。
いわゆる『Access のクロス集計クエリー』の実装を試みるなら、てっとり早いのは、複数のサブクエリーを使う事ですが、それでは列毎にSQL文が実行される事になるので、あまりにもパフォーマンスが悪すぎます。
以下の方法で実装すれば、サブクエリーを使用する方法よりは効率のよいSQLになります。但し、横列は固定になる点に注意してください。横列が不定の場合は、動的SQLなどを使用して一時表やビューを作る必要があるでしょう。
年を縦、月を横にした販売数量合計を表示します。
select to_char(saledt,'yyyy') "年",
nvl(sum(decode(substr(to_char(saledt,'mm'),1,2),'01',qty,0)),0) "1月",
nvl(sum(decode(substr(to_char(saledt,'mm'),1,2),'02',qty,0)),0) "2月",
nvl(sum(decode(substr(to_char(saledt,'mm'),1,2),'03',qty,0)),0) "3月",
nvl(sum(decode(substr(to_char(saledt,'mm'),1,2),'04',qty,0)),0) "4月",
nvl(sum(decode(substr(to_char(saledt,'mm'),1,2),'05',qty,0)),0) "5月",
nvl(sum(decode(substr(to_char(saledt,'mm'),1,2),'06',qty,0)),0) "6月",
nvl(sum(decode(substr(to_char(saledt,'mm'),1,2),'07',qty,0)),0) "7月",
nvl(sum(decode(substr(to_char(saledt,'mm'),1,2),'08',qty,0)),0) "8月",
nvl(sum(decode(substr(to_char(saledt,'mm'),1,2),'09',qty,0)),0) "9月",
nvl(sum(decode(substr(to_char(saledt,'mm'),1,2),'10',qty,0)),0) "10月",
nvl(sum(decode(substr(to_char(saledt,'mm'),1,2),'11',qty,0)),0) "11月",
nvl(sum(decode(substr(to_char(saledt,'mm'),1,2),'12',qty,0)),0) "12月"
from ksale
group by to_char(saledt,'yyyy');
最大値と等しいデータを検索するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
顧客単位の最大販売数量を売ったスタッフの一覧を表示します。
--FROM句のインラインビューが使える場合（R8.0以降）
select s.saledt,e.empnm,c.custnm,v.maxqty
from ksale s, kemp e, kcust c,
(select custno,max(qty) as maxqty from ksale group by custno) v
where s.custno = v.custno
and s.qty = v.maxqty
and s.empno = e.empno
and s.custno = c.custno
order by 1,2;
--FROM句のインラインビューが使えない場合（相関サブクエリー）
select s.saledt,e.empnm,c.custnm,s.qty from ksale s, kemp e, kcust c
where s.empno = e.empno
and s.custno = c.custno
and exists
(select custno,max(qty) from ksale
where custno = s.custno
group by custno
having max(qty)= s.qty
)
order by 1,2;
--FROM句のインラインビューが使えない場合（オラクルではこんな方法も可能）
select s.saledt,e.empnm,c.custnm,s.qty
from ksale s, kemp e, kcust c
where s.empno = e.empno
and s.custno = c.custno
and (s.custno,s.qty) in (select custno,max(qty) as maxqty from ksale group by custno)
order by 1,2;
AテーブルにあってBテーブルにないデータを検索するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
売上テーブルに存在しない社員の一覧を表示します。
--FROM句のインラインビューが使える場合（R8.0以降）
select e.empnm from kemp e,
(select empno from kemp
minus -- MINUS：ORACLEでのみ可能な非存在連結用UNION
select empno from ksale) v
where e.empno = v.empno;
--FROM句のインラインビューが使えない場合（相関サブクエリー）
select e.empnm from kemp e
where not exists
(select 'X' from ksale s
where s.empno = e.empno
);
『グループ単位での』上位ｎ件を検索するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
ORACLEであれば 8i以降、MSSQLであれば 2005以降で、RANKなどの分析関数が使用可能ですが、以下の例は、ANSIエントリーレベルで実現でき、大抵のRDBMSで流用できます。
※
河端善博さんのブログにおいて、この辺りの事が議論されています。
より高速で効率的な例が載っていますので、ご覧あれ。
社員単位で、販売数量の大きかった順に最大3件ずつ（売上が3件に満たない場合もある）
売上一覧を表示します。
（＝ある人が最も沢山売ったときの数量はいくつか？人単位で上位３つを示せ）
--前に書いていたのがかなり結果がおかしかったので変えました
select e.empnm,v.qty
from
(
select distinct s1.empno,s1.qty
from ksale s1
where qty <= (select max(s2.qty)
from ksale s2
where s2.empno = s1.empno
and s2.qty >= s1.qty
having count(distinct s2.qty) <= 3)
) v ,
kemp e
where v.empno = e.empno
order by v.empno,v.qty desc;
--ついでに分析関数を使った例（上とは結果もちょっと違いますけどね）
--（rank と dense_rank の振る舞いの差は「同着だったらどうすんの？」と言う事）
select * from
(
select distinct e.empnm,e.empno,v.qty
from kemp e
inner join
(select empno,qty,rank() over (partition by empno order by qty desc) rank from ksale) v
-- (select empno,qty,dense_rank() over (partition by empno order by qty desc) rank from ksale) v
on e.empno = v.empno
where v.rank <= 3
)
order by empno, qty desc;
ソートでNULL列の行の順番を上に(下に)するには？ ▲TOP
ORACLE MSSQL SSA Postgres Jet
社員マスタの取締役社長のデータには部署番号が入っていません。
ソート時にNULL列の行を明示的に上に(もしくは下に)表示します。
--ORACLEの場合は、NULL列の行が一番下に表示されてしまうので、NVLで最小値に変えてソートする
select * from kemp
order by nvl(deptno,0);
Aテーブルの列の内容でBテーブルを更新するには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
新単価テーブルに該当する商品マスタについて、新しい単価に更新します。
--新単価テーブル作成
create table knewprice
(prodno number(4), --商品番号
price number(6) --単価
);
--新単価を追加する
insert into knewprice values (1,1200);
insert into knewprice values (2,1300);
--新単価テーブルの新しい単価で商品マスタの単価を更新（該当しないものはそのまま）
update kprod p
set p.price =
(select new.price from knewprice new
where new.prodno = p.prodno
)
where exists
(select 'x' from knewprice new
where new.prodno = p.prodno
);
※新単価が商品マスタのすべての商品を満たす場合、exists（または in ）による
相関副問合わせは、不要です。
近似値を求めるには？ ▲TOP
ORACLE MSSQL SSA Postgres Jet
absを使って近似値を求める例です。
--条件に一番近い日付（前後は問わない）のデータ
select *
from ksale
where abs(saledt - to_date('2000-11-09','yyyy-mm-dd')) =
(select min(abs(saledt - to_date('2000-11-09','yyyy-mm-dd')))
from ksale
where saledt != to_date('2000-11-09','yyyy-mm-dd')
);
--条件に一番近い過去日付のデータ
select *
from ksale
where abs(saledt - to_date('2000-11-09','yyyy-mm-dd')) =
(select min(abs(saledt - to_date('2000-11-09','yyyy-mm-dd')))
from ksale
where saledt < to_date('2000-11-09','yyyy-mm-dd')
);
--条件に一番近い未来日付のデータ
select *
from ksale
where abs(saledt - to_date('2000-11-09','yyyy-mm-dd')) =
(select min(abs(saledt - to_date('2000-11-09','yyyy-mm-dd')))
from ksale
where saledt > to_date('2000-11-09','yyyy-mm-dd')
);
※言うまでもなく、min を max にすれば、「条件に最も遠い」データ
が求まります。
※ここでは日付の近似値を検索する例を挙げていますが、上記のように、
「比較対照と条件の差」が求められるのであれば、min(abs(比較対照 - 条件))
を検索条件にして、様々なデータ型の近似値が求められます。
グルーピングした時の合計や件数を条件にするには？ ▲TOP
ORACLE MSSQL SSA Postgres MySQL Jet
「グループ単位での上位ｎ件の求め方」でも使用しているように、
「GROUP BY でグルーピングした後の」合計や件数を条件として使いたい場合があります。
WHERE句を使って、この要件を満たすには、インラインビューを使用すれば
（それが使用可能なRDBMSであれば）可能ですが、
もっと効率的な方法としては、HAVING COUNT を使用します。
--3件以上の売上がある社員の社員番号は？（インラインビュー）
select v.*
from
(
select empno,count(*) cnt
from ksale
group by empno
) v
where v.cnt >= 3
order by v.cnt desc;
--3件以上の売上がある社員の社員番号は？（HAVING COUNT）
select empno,count(*)
from ksale
group by empno
having count(*) >= 3
order by count(*) desc;
列の内容を16進（ヘキサダンプ）で表示するには？ ▲TOP
ORACLE MSSQL
列の内容を16進で表示します。
select dump('予定表' || chr(10),16) from dual;
--または（こちらの方が楽かな）
select rawtohex('予定表' || chr(10)) from dual;
階層問い合わせをするには？ ▲TOP
オラクルでは以下の様な独自拡張を使用して、階層問い合わせを行います。
よくあるSCOTT.EMPの例ではなくトランプを例にしてみます。
create table cards
(cno number(4),
cname varchar2(5),
cmark varchar2(10),
cclass varchar2(5),
higher_than_me number(4)
);
insert into cards values (1,'JOKER',NULL,'SUPER',null);
insert into cards values (2,'ACE','SPADE','HIGH','1');
insert into cards values (3,'ACE','HEART','HIGH','1');
insert into cards values (4,'ACE','CLUB','HIGH','1');
insert into cards values (5,'ACE','DIAMOND','HIGH','1');
insert into cards values (6,'KING','SPADE','MID','2');
insert into cards values (7,'KING','HEART','MID','3');
insert into cards values (8,'KING','CLUB','MID','4');
insert into cards values (9,'KING','DIAMOND','MID','5');
insert into cards values (10,'QUEEN','SPADE','MID','6');
insert into cards values (11,'QUEEN','HEART','MID','7');
insert into cards values (12,'QUEEN','CLUB','MID','8');
insert into cards values (13,'QUEEN','DIAMOND','MID','9');
insert into cards values (14,'JACK','SPADE','LO','10');
insert into cards values (15,'JACK','HEART','LO','11');
insert into cards values (16,'JACK','CLUB','LO','12');
insert into cards values (17,'JACK','DIAMOND','LO','13');
insert into cards values (18,'10','SPADE','LO','14');
insert into cards values (19,'10','HEART','LO','15');
insert into cards values (20,'10','CLUB','LO','16');
insert into cards values (21,'10','DIAMOND','LO','17');
--ダイヤのカードをクラス「HIGH」から強い順に表示
SELECT LPAD(' ',2*(LEVEL-1)) || cname || ':' || cmark "役"
FROM cards
WHERE cmark = 'DIAMOND'
START WITH cclass = 'HIGH'
CONNECT BY PRIOR cno = higher_than_me;
--クラス「SUPER」から強い順に3階層まで表示
SELECT LPAD(' ',2*(LEVEL-1)) || cname || ':' || cmark "役"
FROM cards
START WITH cclass = 'SUPER'
CONNECT BY PRIOR cno = higher_than_me
AND LEVEL <= 3;
--※大抵の例文で LPAD , LEVEL を使っているが、
-- 見た目を整えるためなので無くてもよい
SELECT cname || ':' || cmark "役"
FROM cards
START WITH cclass = 'SUPER'
CONNECT BY PRIOR cno = higher_than_me
AND LEVEL <= 3;
デバッグ用縦表示リストSELECT文生成 ▲TOP
開発時に便利な縦表示の見やすいリストを作成するためのスクリプト
-- gendebug.sql
-- Usage :
-- SQL> @gendebug
--
set heading off
set verify off
set feed off
set pagesize 0
accept tblnm prompt テーブル名入力：
spool &tblnm._deb.sql.
prompt set hea off;
prompt set pages 0;
prompt select '** ' || rownum || ' *********************************************
*************' || chr(10) ||
select
chr(39) || COLUMN_NAME || '：' || chr(39) || ' || ' ||
COLUMN_NAME || ' || ' || 'chr(10) || '
from USER_TAB_COLUMNS
where TABLE_NAME = upper('&tblnm')
order by COLUMN_ID
/
prompt ''
prompt from &tblnm;;
prompt set hea on;;
prompt set pages 24;;
spool off;
set heading on;
set feed on;
set pagesize 24
ROWNUM を使用した擬似スクロール・カーソル ▲TOP
ORACLE7では、次の様に rownum を between で括る事ができました
（パフォチュー的な観点から私は between 使いませんが）。
select * from emp where rownum <= 5 and rownum >= 10;
しかし、この手法は、ORACLE7以前のバージョンでのみ可能で、
ORACLE8からは使えなくなっています
（エラーにはならないが、結果は帰ってこない）。
現在の仕様だと、rownum は『取得した結果セットの何番目か』
と言う風にしか判断できないのです。
ですからORACLE8以降では
where rownum = 1
where rownum < number
where rownum <= number
しか通りません。
しかし、次の様にすればORACLE8でも可能です。
select *
from (select tbl.*, rownum rn from tbl)
where rn >= n1 and rn <= n2;
このスクリプト自体は、かなり昔 NIFTYのオラクル・ステーションSORACLE（まだステーションになってなかった頃？）
で拾ったものを参考にしています。
これは、SELECT文のみでスクロールカーソルの様な機能を実現します（n1 と n2 を任意の範囲でインクリメント/デクリメント
すればよい）。
もちろん、パフォーマンスは保証できるものではなく、本当のカーソルではありませんから
フェッチ時(笑)の整合性も保証されませんし。スケーラビリティの面でもまったく好ましいものではありません。
但し、既存システムをORACLE7からORACLE8に変更した際に、ORACLE8でのROWNUM の仕様変更に苦しむような場合、
ここに書いた事が、ひょっとしたら逃げ道になるかもしれません。
因みに、某掲示板では次のような書かれ方をしていました。こちらの方が、
パフォーマンス的には良好です。
select * from tbl where rownum <= 10
minus
select * from tbl where rownum < 5;
BLOB型のあいまい検索もどき ▲TOP
これは、BLOB型等のあいまい検索について某掲示板で回答した時のSQLです。
大きいオブジェクト型のデータ（例えば画像ファイル）の、『似ている度』
を判断して検索するための実装案です。
-- blobcompare.sql
select b.key_value --似ていると思われるデータ
from lob_table a, --比較元
lob_table b --比較対象（自己結合）
where a.key_value = :hoge --比較元を特定する一意の条件
and b.key_value! = :hoge --自分は弾く
and DBMS_LOB.COMPARE(a.b_col,b.b_col,:n,1,1)=0 --オフセットから nバイト比較
この引数 n のバイト数を調節して『0 なら似てる。以外は似てない』
と判断するわけです。
ファイルタイプがGIFのみ、とかであれば、この方法で何となく、
それらしい結果が得られるかもしれません。ただし、
『内容が似ていてもバイナリレベルではまったく異なる』ようなファイル
（→例えば同じ画像イメージを持っているJPGとGIFの場合）
なんかだと使えないでしょう。
それと、パフォーマンスに関してもかなり疑問ありです
（もっとも、どのような実装方法であっても速くはならないような気はします）。
ソートした先頭からｎ件表示するには？ ▲TOP
8i 以降であればFROM句のインラインビューで ORDER BY が使えるので簡単ですが、
それ以前のバージョンではちょっとしたトリックが必要です。
これは NIFTYのオラクル・ステーションSORACLE で拾った方法です。
何故、これでうまく行くかと言うと、プランを見てもらえばわかるのですが、結合時に
マージジョインする事で『暗黙的にソートされている』からです。
ただし、この方法は昇順でのみ可能である点と、オプティマイザのバージョンによっては使えない可能性
がある点に留意してください。
-- sorttop.sql
-- Usage :
-- SQL> @sorttop
--
select * from ksale , dual
where to_char(custno) = dummy (+)
and rownum <= 3
;
このページの先頭へ
ORACLE
[ SQL ]
[ データベースの情報 ]
[ 手続き型言語 ]
[ その他 ]
Works > 逆引きSQL比較 > ORACLE > SQL