SAK 図書館

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

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 編資料
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | フリーソフト |

SAK Streets メインストリート 図書館・カフェ 一覧 SAK Streets 検索



Copyright (C) 1999-2017 Y.SAK