SAK 図書館
SAK 図書館
SQL 基礎実地編 (その六) 問い合わせ、集計、重複レコード、集合、複合
基本的に Oracle、SQL-Server、MDB で共通です。
■集計問い合わせ(合計値)
・集計問い合わせを行うには、group by 句を使用する。
この例では、品番の先頭が「a」で始まる明細を品番単位に集計(サマリー)し、
受注数の合計が 1000 以上のものを問い合わせる。
having 句は、集計後の結果に対して抽出条件が指定できる。
select 品番, sum(受注数) from sak.受注m
where 品番 like 'a%'
group by 品番
having sum(受注数) >= 1000
order by 品番
;
・count() を使用すると、合計件数が問い合わせられる。
(問い合わせ件数表示、照会件数表示、抽出件数)
この例では、品番の先頭が「a」で始まる明細を品番単位に集計し、受注数
の合計が 1000 以上のものを問い合わせる。
having 句は、集計後の結果に対して抽出条件が指定できる。
select 品番, count(*), sum(受注数) from sak.受注m
where 品番 like 'a%'
group by 品番
having sum(受注数) >= 1000
order by 品番
;
・単純に特定の明細のレコード件数を問い合わせるときは、次のように使用す
る。この例では、品番の先頭が「a」で始まる明細の件数が得られる。
select count(*) from sak.受注m
where 品番 like 'a%'
;
・avg を使用すると平均を問い合わせることができる。
この例では、品番毎の受注数の平均が得られる。
select 品番, avg(受注数) from sak.受注m
group by 品番
order by 品番
;
・max、min を使用すると最大値と最長値を問い合わせることができる。
この例では、品番毎の受注数の最大と最小が得られる。
select 品番, max(受注数), min(受注数) from sak.受注m
group by 品番
order by 品番
;
■重複レコード除去(重複レコード削除)
・テストm のキーがユニークでないと仮定すると、同じキーのレコードが複数
存在する事になる。
distinct を指定すると重複レコードを取り除いて問い合わせできる。
distinct の代わりに unique と指定しても同じである。
(重複データ)
select distinct
キー
from sak.テストm
order by キー
;
select unique
キー
from sak.テストm
order by キー
;
■集合問い合わせ
・union all を使用すると問い合わせ結果をマージできる。
この例では、受注1m と受注2m の品番の先頭が「a」で始まるものが問い合
わされる。union all の代わりに union とだけ指定すると、重複レコード
は含まれない。(表を合わせ、付け足す)
select * from sak.受注1m
where 品番 like 'a%'
union all
select * from sak.受注2m
where 品番 like 'a%'
;
■集合差問い合わせ
・minus、minus all を使用すると問い合わせ結果を引き算できる。
この例では、受注m の品番の先頭が「a」で始まるものが問い合わせ、minus
で、得意先CD「1000」を取り除いている。
(レコード比較、レコード不足抽出、差分結合、テーブル差分)
select * from sak.受注m
where 品番 like 'a%'
minus
select * from sak.受注m
where 得意先CD = '1000'
;
■集合結合問い合わせ
・intersect を使用すると両方の問い合わせ共通に戻されるレコードだけが
戻る。
select * from sak.受注m
where 品番 like 'a%'
intersect
select * from sak.受注m
where 得意先CD = '1000'
;
■複合問い合わせ
・in を使用すると副問い合わせの結果で、問い合わせることができる。
この例では、受注2m の品番の先頭が「a」で始まるものから重複レコードを
除いて、受注1m の品番を抽出している。
(副問合せ、サブクエリ、副参照、複問い合わせ、副 select 文)
select * from sak.受注1m
where 品番 in
(select distinct 品番 from sak.受注2m where 品番 like 'a%')
;
・この例では、受注2m の品番の先頭が「a」で始まるものから重複レコードを
除いて、その結果以外の 受注1m の品番を抽出している。
select * from sak.受注1m
where 品番 not in
(select distinct 品番 from sak.受注2m where 品番 like 'a%')
;
・any は、副問い合わせのいずれかと言う条件になる。
この例では、副問い合わせのいずれかより大きければ抽出される。
但し、この例は悪い。現実的な使い方をして下さい。
select * from sak.受注1m
where 品番 > any
(select distinct 品番 from sak.受注2m where 品番 like 'a%')
;
・all は、副問い合わせの全てにと言う条件になる。
この例では、副問い合わせの全てより大きければ抽出される。
但し、この例は悪い。現実的な使い方をして下さい。
select * from sak.受注1m
where 品番 > all
(select distinct 品番 from sak.受注2m where 品番 like 'a%')
;
・例えば、同じ品番の内で、最大の受注数レコードだけを得るには、
次のように all を使用する。
このような問い合わせを、相関問い合わせ、相関副問い合わせと呼ぶらしい。
副問い合わせのパラメタに主問い合わせの項目が使用されるからだろうか。
select * from sak.受注m a
where a.受注数 >= all
(select 受注数 from sak.受注m b where b.品番 = a.品番)
;
・例えば、同じ品番の内で、最新の受注日のレコードだけを得るには、
次のように all を使用する。(最新日付抽出)
select * from sak.受注m a
where a.受注日 >= all
(select 受注日 from sak.受注m b where b.品番 = a.品番)
;
・副問い合わせから、更に問い合わせることもできる。
この例では、最初に品番の先頭が「a」で始まるものを抽出して、その結果
に対して、受注数 が 100 以上の抽出を行っている。
但し、この例の結果なら、もっと簡単に一度に求められる。
from 句に副問い合わせを使うことをインラインビューと呼ぶらしい。
select * from
(select * from sak.受注m where 品番 like 'a%')
where 受注数 >= 100;
;
・得意先毎の最小受注数の品番を問い合わせるには、次のようにする。
最低単価や最大単価でもそうだが、求める結果が得意先毎に単一ではない
可能性を忘れてはならない。
select 得意先CD, 品番, 受注数 from 受注m a
where 受注数 = (select min(受注数) from 受注m where 得意先CD = a.得意先CD)
order by 得意先CD, 品番
;
得意 品番 受注数
---- ------------------ ----------
1000 a001 6
1000 b001 6
2000 a001 10
・上と同等のことは分析ファンクションでも可能のようである。
ただ、「where 受注数 = a受注数」を直に書けないので、結局、面倒な事に
なってしまう。
select 得意先CD, 品番, 受注数 from
(
select 得意先CD, 品番, 受注数,
min(受注数) over (partition by 得意先CD) a受注数
from 受注m
)
where 受注数 = a受注数
order by 得意先CD, 品番
得意 品番 受注数
---- ------------------ ----------
1000 a001 6
1000 b001 6
2000 a001 10
■SQL 基礎実地編資料
■SQL 基礎編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■MySQL 編資料
Copyright (C) 1999-2018 Y.SAK