SAK 図書館
SAK 図書館
SQL 問い合わせ - スカラー副問い合わせ、cursor、row_number、over、with
■スカラー副問い合わせ (Oracle9i 以降)
・最近の Oracle は何でもありです。
このスカラー副問い合わせは、広く応用できて非常に便利です。
尚、フィールドなど単一行の結果を戻す必要のある問い合わせを
単一行副問い合わせと呼ぶ。
(単1行副問い合わせ、単一行問い合わせ、単1行問い合わせ)
select
受注番号,
品番,
(select 在庫数 from 品番v1 b where b.品番 = a.品番) 在庫数
from 受注m a
;
select
受注番号,
品番,
(select sum(受注数) from 受注m b where b.品番 = a.品番) 受注数計
from 受注m a
;
select
品番,
コード1,
(select コード名 from コードm b where b.コード = a.コード1) コード名1,
コード2,
(select コード名 from コードm b where b.コード = a.コード2) コード名2
from 品番m a
;
・from 句に副問い合わせを書くこともできる。
この書式は Oracle8 ぐらいから使えた。
これをインラインビューと呼ぶ。
select *
from (select * from 受注m where 品番 like 'a%') a
where a.受注数 > 1000
;
■CURSOR (Oracle9i 以降)
・cursor() は、このスカラー副問い合わせに似ているのですが、こちらは
複数の列を返すことが出来ます。
select
受注番号,
品番,
cursor(
select 在庫数, 受注数 受注累積 from 品番v1 b where b.品番 = a.品番
) 在庫数
from 受注m a
;
■ROWNUM (Oracle8i 以降?)
・先頭から 10 件問い合わせるには、次のようにする。
access の top と同じことができる。
row_number の方も非常に便利である。
(TOP 10 取得、表示行制限、レコード数限定、件数指定、表示件数)
(1 レコード取得、1 件取得)
select rownum, 受注番号, 品番, 受注数 from 受注m
where rownum <= 10
;
ROWNUM 受注番号 品番 受注数
---------- ---------- ------------------ ----------
1 000001-002 a001 500
2 000001-001 a001 70
3 000001-056 a001 500
4 000001-057 b001 2000
5 000001-058 a001 500
6 000001-059 a001 500
7 000001-060 a001 500
8 000001-061 c001 1500
9 000001-062 a001 500
10 000001-063 a001 501
・但し、order by 句があると、rownum の順序が変わってしまう。
select rownum, 受注番号, 品番, 受注数 from 受注m
where rownum <= 10 order by 受注数 desc;
;
ROWNUM 受注番号 品番 受注数
--------- ---------- ------------------ ----------
4 000001-057 b001 2000
8 000001-061 c001 1500
10 000001-063 a001 501
1 000001-002 a001 500
3 000001-056 a001 500
6 000001-059 a001 500
9 000001-062 a001 500
7 000001-060 a001 500
5 000001-058 a001 500
2 000001-001 a001 70
・order by 句がある場合、次のようにすると良い。
select rownum, 受注番号, 品番, 受注数 from
(select 受注番号, 品番, 受注数 from 受注m
order by 受注数 desc)
where rownum <= 10
;
ROWNUM 受注番号 品番 受注数
---------- ---------- ------------------ ----------
1 000011-001 b001 6000
2 000019-001 b001 5555
3 100011-002 c001 4500
4 000004-001 a001 4000
5 000546-001 b001 3000
6 000001-057 b001 2000
7 000001-061 c001 1500
8 000001-078 a001 549
9 000001-063 a001 501
10 000001-002 a001 500
・rownum = 2、rownum > 1 は使えない。
select rownum, 受注番号, 品番, 受注数 from
(select 受注番号, 品番, 受注数 from 受注m
order by 受注数 desc)
where rownum > 2
;
** 処理不能 - レコードが選択されませんでした
■ROW_NUMBER (Oracle9i 以降?)
・シーケンス番号を返してくれる。
使い方で access の top と同じことができる。
但し、rownum の方が処理は速いかもしれない。
(TOP 10 取得、表示行制限、レコード数限定、件数指定、表示件数、TOP 値)
(何件目のレコード、1レコード表示、1レコードのみ表示、トップ値)
(トップ N 分析、トップ N 問い合わせ、TOP N 分析、TOP N 問い合わせ)
(1 レコード取得、1 件取得)
select row_number() over (order by 受注数 desc) ct
, 受注番号, 品番, 受注数 from 受注m order by 受注数 desc;
・先頭から 10 件問い合わせるには、次のようにする。
select * from (
select row_number() over (order by 受注数 desc) ct
, 受注番号, 品番, 受注数 from 受注m order by 受注数 desc
) where ct <= 10
;
CT 受注番号 品番 受注数
---------- ---------- ------------------ ----------
1 000011-001 b001 6000
2 000019-001 b001 5555
3 100011-002 c001 4500
4 000004-001 a001 4000
5 000546-001 b001 3000
6 000001-057 b001 2000
7 000001-061 c001 1500
8 000001-078 a001 549
9 000001-063 a001 501
10 000001-002 a001 500
・先頭レコードを問い合わせるには、次のようにする。
(1 件取得)
select * from (
select row_number() over (order by 受注数 desc) ct
, 受注番号, 品番, 受注数 from 受注m order by 受注数 desc
) where ct = 1
;
CT 受注番号 品番 受注数
--------- ---------- ------------------ ----------
1 000011-001 b001 6000
・最終からと言うのは、元になる SQL のソートオーダを逆にする。
ソートオーダを逆に先頭から 10 件問い合わせるには、次のようにする。
select * from (
select row_number() over (order by 受注数) ct
, 受注番号, 品番, 受注数 from 受注m order by 受注数
) where ct <= 10
;
CT 受注番号 品番 受注数
--------- ---------- ------------------ ----------
1 000628-001 a001 10
2 000746-001 a001 10
3 000921-001 c001 10
4 000010-001 b001 20
5 000002-001 a001 60
6 000003-001 a001 60
7 000001-001 a001 70
8 000643-001 a001 100
9 006589-001 a001 100
10 730001-001 a001 100
・rownum では指定できない条件も row_number ならできる。
select * from (
select row_number() over (order by 受注数) ct
, 受注番号, 品番, 受注数 from 受注m order by 受注数
) where ct >= 10 and ct <= 20
;
CT 受注番号 品番 受注数
--------- ---------- ------------------ ----------
10 730001-001 a001 100
11 741001-001 a001 100
12 006589-002 a001 100
13 000800-001 a001 100
14 002301-001 a001 101
15 004601-001 a001 101
16 100011-001 b001 111
17 100011-003 b001 111
18 000001-002 a001 500
19 000001-056 a001 500
20 000001-059 a001 500
■CASE (Oracle9i 以降)
・ほんと、なんでもできるなあ〜。
select 受注番号, 品番,
case
when 受注数 < 1000 then '少ない'
else '多い'
end
from 受注m;
・受注数が 1000 以上のものだけ合計する。
select 品番, sum(
case
when 受注数 < 1000 then 0
else 受注数
end)
from 受注m group by 品番;
■EXISTS (Oracle8i 以降?)
・副問い合わせが行を戻したか。
select * from 受注m a
where exists (select * from 品番m b where b.品番 = a.品番);
■FIRST、LAST (Oracle9i 以降)
・複雑すぎてちょっと難解である。
select 品番,
max(受注数),
min(受注数)
from 受注m group by 品番
;
・次の例は、最も新しい更新日付の品番の最大受注数と
最も古い更新日付の品番の最小受注数が返る。
select 品番,
max(受注数) keep (dense_rank last order by 更新日付) a,
min(受注数) keep (dense_rank first order by 更新日付) b
from 受注m group by 品番
;
・明細で返すこともできる。
これも使い道がいまひとつわからない。
select 品番,
max(受注数) keep (dense_rank last order by 更新日付)
over (partition by 品番) a,
min(受注数) keep (dense_rank first order by 更新日付)
over (partition by 品番) b
from 受注m order by 品番
;
■WITH (Oracle9i 以降)
・複雑な SQL をすっきり書くためのものなのだろうか。
with
sel1 as (
select * from 受注m order by 受注番号
),
sel2 as (
select avg(受注数) 受注数 from 受注m
)
select * from sel1 where 受注数 > (select * from sel2)
;
・sel2 で sel1 を使用することもできるらしい。
けっこう複雑な SQL が作れそうである。
with
sel1 as (
select * from 受注m order by 受注番号
),
sel2 as (
select avg(受注数) 受注数 from sel1
)
select * from sel1 where 受注数 > (select * from sel2)
;
■FIRST_VALUE、LAST_VALUE (Oracle8i 以降)
・いまひとつ使い道がわかんない。
条件に一致する最初、または、最後の 1 件を取得する。
select
受注番号,
品番,
first_value(品番)
over (order by 品番 asc rows unbounded preceding) as 品番,
last_value(品番)
over (order by 品番 asc rows unbounded preceding) as 品番
from 受注m
order by 受注番号
;
■GROUP_ID (Oracle9i 以降)
・いまひとつ使い道がわかんない。
select 品番,
max(受注数),
min(受注数),
group_id()
from 受注m group by 品番
;
■SQL 基礎実地編資料
■SQL 基礎編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
Copyright (C) 1999-2018 Y.SAK