FrontPage




Firebird SQLリファレンス SELECT

 1つ以上のテーブルからデータを検索します。ストアドプロシージャの呼出しにも 使用されます。SQL,DSQLおよびisqlから使用出来ます。
 この文書は、公開されているIB6のSQLレファレンスを基とし、1.5.1までの 各リリースノートにおける追加内容に関して反映・統合されています。

構文

  • 基本構文
SELECT [TRANSACTION transaction]
   [FIRST int] [SKIP int]
   [DISTINCT | ALL]
   {* | <val> [, <val> …]}
   [INTO :var [, :var …]]
   <case expression>
   FROM <tableref> [, <tableref> …]
   [WHERE <search_condition>]
   [GROUP BY <group_by_list>]
   [HAVING <search_condition>]
   [UNION <select_expr> [ALL]]
   [PLAN <plan_expr>]
   [ORDER BY <order_list>]
   [FOR UPDATE [OF col [, col …]]]
   [WITH LOCK]
;
<val> = {
    col [<array_dim>] | :variable
    | <constant> | <expr> | <function>
    | udf ([<val> [, <val> …]])
    | NULL | USER | RDB$DB_KEY | ?
} [COLLATE collation] [AS alias]
<array_dim> = [[x:]y [, [x:]y …]]
<constant> = num | 'string' | charsetname 'string'
<function> = COUNT (* | [ALL] <val> | DISTINCT <val>)
    | SUM ([ALL] <val> | DISTINCT <val>)
    | AVG ([ALL] <val> | DISTINCT <val>)
    | MAX ([ALL] <val> | DISTINCT <val>)
    | MIN ([ALL] <val> | DISTINCT <val>)
    | CAST (<val> AS <datatype>)
    | UPPER (<val>)
    | GEN_ID (generator, <val>)
<tableref> = <joined_table> | table | view | procedure
    [(<val> [, <val> …])] [alias]
<joined_table> = <tableref> <join_type> JOIN <tableref>
    ON <search_condition> | (<joined_table>)
<join_type> = [INNER] JOIN
    | {LEFT | RIGHT | FULL } [OUTER]} JOIN
<search_condition> =
    <val> <operator> {<val> | (<select_one>)}
    | <val> [NOT] BETWEEN <val> AND <val>
    | <val> [NOT] LIKE <val> [ESCAPE <val>]
    | <val> [NOT] IN (<val> [, <val> …] | <select_list>)
    | <val> IS [NOT] NULL
    | <val> {>= | <=}
    | <val> [NOT] {= | < | >}
    | {ALL | SOME | ANY} (<select_list>)
    | EXISTS (<select_expr>)
    | SINGULAR (<select_expr>)
    | <val> [NOT] CONTAINING <val>
    | <val> [NOT] STARTING [WITH] <val>
    | (<search_condition>)
    | NOT <search_condition>
    | <search_condition> OR <search_condition>
    | <search_condition> AND <search_condition>
<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}
<plan_expr> =
    [JOIN | [SORT] [MERGE]] ({<plan_item> | <plan_expr>}
    [, {<plan_item> | <plan_expr>} …])
<plan_item> = {table | alias}
    {NATURAL | INDEX (<index> [, <index> …])
             | ORDER <index>}
<order_list> =
    {col | int} [COLLATE collation]
    [ASC[ENDING] | DESC[ENDING]] [<nulls_placement>]
    [, <order_list> …]
<nulls_placement> : NULLS FIRST | NULLS LAST;

従来形式

<group_by_list> =
    col [COLLATE collation] [, col [COLLATE collation] …]

1.5以降(旧形式も使用可能)

<group_by_list> = <group_by_item> [, <group_by_list>];
<group_by_item> = column_name
    | degree (ordinal)
    | udf
    | <group_by_function>
;
<group_by_function> = <numeric_value_function>
    | <string_value_function>
    | <case_expression>
;
<numeric_value_function> =
    EXTRACT '(' timestamp_part FROM value ')';
<string_value_function> =
    SUBSTRING '(' value FROM pos_short_integer ')'
    | SUBSTRING '(' value FROM pos_short_integer FOR
        nonneg_short_integer ')'
    | KW_UPPER '(' value ')'
;

※1 <case expression>に関してはCASEを参照。
※2 <group_by_list>に関しては強化された GROUP BY の機能を参照


search_conditionの付表:

引数説明
expr1つの値を返すSQL式
select_one1つの列・値を返すSELECT
select_list1つの列で、0あるいは複数行のリストを返すSELECT
select_expr0あるいは複数行の値のリストを返すSELECT



  • 文法の補足
    • 配列(<array_dim>)
       配列を宣言する場合最も外側のブラケットを含まなければなりません。 例えば、次の文では、長さ6文字の文字列で構成される5×5の二次元配列になります。
      my_array = varchar(6)[5,5]
       添え字の開始値が1以外の配列を指定するにはコロン(:)を使用してください。 次の例では、20で始まり30で終わる整数型の配列となります。
      my_array = integer[20:30]

    • プレースホルダ(?)
       SQL及びisqlでは、プレースホルダは使用できません。DSQLでは使用できます。

    • BLOB列に対するCOLLATE
       BLOBの列に対してはCOLLATEを指定することは出来ません。

    • 終端文字(;)
       DSQLやC/C++などのプログラミングでの組込みの場合は、終端文字は必要ありません。

    • 引数に関する補足
       (訳注)説明も参照して下さい。 内容が元のドキュメント上で多少重複しています。

      引数説明
      TRANSACTION transactionSQLのみ可。指定したトランザクション下で文が実行されます
      SELECT [DISTINCT|ALL]検索するデータを指定します。DISTINCTは重複した値が返されるのを防ぎます。ALL(デフォルト)はすべての値を検索します
      {*|val [, val …]}アスタリスク(*)を指定すると、全ての列を検索します。検索リスト(列名、値、式)を指定することも出来ます
      INTO :var [, var …]組込みSQLによる1行取得のみ可。値を取得するホスト言語変数のリストを指定します
      FROM tableref
      [, tableref …]
      値を検索するテーブル、ビュー、ストアドプロシージャのリストを指定します。JOINの指定やネストしたJOINを含むことが出来ます
      table既存のテーブル名
      view既存のビュー名
      procedureSELECTライクな機能を持つストアドプロシージャの名前
      aliasテーブル、ビュー、列のエイリアス。tablerefで宣言したものは、テーブルやビューの参照に使用できます
      joined_tableJOINで結合するテーブル
      join_typeJOINのタイプ。デフォルトはINNER(内部結合)
      WHERE search_condition検索対象の行の中で、ある条件で行を限定するために使用します
      GROUP BY col [, col …]指定された列リストに基づいて、検索結果をグループ分けします
      COLLATE collation検索結果のコレーションオーダーを指定します
      HAVING search_conditionGROUP BYと併用します。指定した条件により返される行グループを限定します
      UNION [ALL]2つ以上の、全体または部分的に同じ構造を持つテーブルを結合します。ALLを指定した場合、同じ行を1つにまとめません
      PLAN plan_exprオプティマイザに検索時のアクセスプランを指定します
      plan_itemアクセスプランのテーブルとインデックスの処理を指定します
      ORDER BY order_list列によるソート順を指定します。列名及び優先順位を、昇順または降順のソート法(ASC or DESC) を指定できます
      int整数または整数式。式の場合は括弧"()"で囲む。

詳細の説明

 SELECTにより、テーブル、ビュー、ストアドプロシージャより値を取り出します。 SELECT文のさまざまな記述により、テーブルから、1つ以上の行を取り出すことが 出来ます。

  • 埋め込みSQLを使用するアプリケーションでは、カーソルを使用せずに複数行の 結果が得られる SELECT を行ってはいけません。埋め込みSQLを使用する アプリケーションで複数行の結果を取り扱う場合は、SELECT を DECLARE CURSOR 構文の中で使用することにより、処理を行うことが出来ます。
     isql上においては、SELECTは複数行の結果が得られる検索を行うことが 出来ます。

  • 全てのSELECT文は、2つの必須となる節(SELECT ... FROM)、及び他に必要とする 節(INTO, WHERE, GROUP BY, HAVING, UNION, PLAN, ORDER BY)を指定できます。
     次表に、使用できる節とその説明を示します。
解説単行SELECT複数行SELECT
SELECT検索のための行であることの宣言必須必須
INTO検索された列を格納するためのホスト変数リスト必須使用不可
case expression出力結果の加工を行います。CASEを参照して下さいオプションオプション
FROM検索対象のテーブルの指定必須必須
WHERE得られた前行を、指定した検索条件により選別されたサブグループ化します。
また、サブクエリーが使用できます。(WHERE節のなかに更にSELECTを持つことが出来る)
オプションオプション
GROUP BY指定により、同じ列をグループ化します。HAVING節と同時に使用することもあります。
(FB1.5では、サブクエリーが使用できます)
オプションオプション
HAVING必ずGROUP BY節と同時に使用します。指定条件により返される行グループを限定します。オプションオプション
UNION同一内容の行が存在しない動的テーブルを得るために、単一または複数のSELECTで得られた結果を結合します。オプションオプション
ORDER BY列名または問合せ文中の指定順を指定することによりソートを行います。ソートの順番は、昇順(ASC)と降順(DESC)が指定できます。省略時はASCです。オプションオプション
PLANクエリオプティマイザにクエリプランを指定します。この指定は、未指定で通常に行われる指定よりも優先されます。オプションオプション
FOR UPDATEDECLARE CURSOR文のSELECTで指定した列名(WHERE CURRENT OFでアップデートされうる)を指定します。?オプション
FIRST(FB専用)
検索で得られた全ての行から、(デフォルトでは先頭からの)指定した行数だけの結果を出力する。出力を開始する行を指定したい場合は、SKIP節を併用する。
?オプション
SKIP(FB専用)
指定された行数だけ、検索で得られた行の先頭をスキップする。
?オプション
WITH LOCK(FB1.5)明示的な行ロック。WITH LOCKを参照。オプションオプション


 FB1.5以降で使用可能なCASE節関係(<case expression>)に関してはCASEを参照。

用例

 SELECTは多機能で構文が複雑な文です。ここでは、主にisql でのいくつかの用例を 示します。完全な使用例は"埋め込みSQLガイド(Embedded SQL Guide)"を見てください。

  • 単純な例です。PROJECTテーブルの指定した列を検索します。
    SELECT JOB_GRADE, JOB_CODE, JOB_COUNTRY, MAX_SALARY FROM PROJECT;
  • ワイルドカード(*)により、テーブル(COUNTRIES)の全ての列と行が取得されます。
    SELECT * FROM COUNTRIES;
  • (埋め込みSQL用の例)WHERE節で指定された条件の全ての行を集計関数によりカウントします。
    EXEC SQL
    SELECT COUNT (*) INTO :cnt FROM COUNTRY
    WHERE POPULATION > 5000000;
  • 文中でエイリアスを定義し、WHERE節で列を指定するために使用した例です。
    SELECT C.CITY FROM CITIES C
    WHERE C.POPULATION < 1000000;
  • 2つの列を取り出していますが、2つめの列によりソートされて行が出力されます。
    SELECT CITY, STATE FROM CITIES
    ORDER BY STATE;
  • 左結合(外部結合)での検索を行います。
    SELECT CITY, STATE_NAME FROM CITIES C
    LEFT JOIN STATES S ON S.STATE = C.STATE
    WHERE C.CITY STARTING WITH 'San';
  • クエリオプティマイズプランを設定して検索します。
    SELECT * FROM CITIES
    PLAN (CITIES ORDER CITIES_1);
    ORDER BY CITY
  • 結合された3つのクエリオプティマイズプランを指定しています。 そのうち2つはインデックス付きの列です。
    SELECT * FROM CITIES C, STATES S, MAYORS M
    WHERE C.CITY = M.CITY AND C.STATE = M.STATE
    PLAN JOIN (STATE NATURAL, CITIES INDEX DUPE_CITY,
    MAYORS INDEX MAYORS_1);

Firebirdでの補足事項

 ここは、主にリリースノート類から情報を抜き出して訳してあります。内容に 疑わしい点があった場合や関連項目に関してはより詳しく調べた場合は、 本家にある元となったリリースノートを参照して下さい。

取得行数の制限、取得開始行指定:FIRST,SKIP

  • 記述
SELECT [FIRST (<integer expr m>)] [SKIP (<integer expr n>)]
SELECT FIRST ......   (FB1.5のみ可)
  • 説明
     検索で得られる全ての行のうち、(SKIP指定が無い場合)先頭からFIRSTで指定 された行数のみを出力します。(FB1.5では0が指定可能です。この場合の 検索結果は常にNullとなります)
     SKIPを指定すると、結果の先頭より指定した行数だけ出力をスキップします。
     この2つを組み合わせることにより、BBSのようなアプリケーションを作成 するときに、任意の行から任意の行数だけ結果を取得するというようなことが 容易に可能となります。

    例:
SELECT SKIP (5+3*5) * FROM MYTABLE;
SELECT FIRST (4-2) SKIP ? * FROM MYTABLE;
SELECT FIRST 5 DISTINCT FIELD FROM MYTABLE;

条件による検索列の加工:CASE

  • 記述
<case expression> ::=
    <case abbreviation> | <case specification>
<case abbreviation> ::=
    NULLIF <left paren> <value expression> <comma>
               <value expression> <right paren>
   | COALESCE <left paren> <value expression>
               { <comma> <value expression> }... <right paren>
<case specification> ::=
    <simple case> | <searched case>
<simple case> ::=
    CASE <value expression> <simple when clause>...
    [ <else clause> ]
    END
<searched case> ::=
    CASE <searched when clause>...
    [ <else clause> ]
    END
<simple when clause> ::= WHEN <when operand> THEN <result>
<searched when clause> ::=
    WHEN <search condition> THEN <result>
<when operand> ::= <value expression>
<else clause> ::= ELSE <result>
<result> ::= <result expression> | NULL
<result expression> ::= <value expression>
  • 説明
     1.5で追加された構文です。
     指定した条件により、検索で得られた列の結果を加工します。
     条件の指定は省略形と仕様形があり、仕様形はさらにシンプルケースと サーチドケースの二種類があります。省略形のために、 NULLIF,COALESCEの2つが追加されています。 両者に関しては本項の後に続く項で解説しています。
     シンプルケースは、CASEで式(列名など)を指定してWHENに合致条件を書きます。
     サーチドケースは、WHENに続いて検索条件式を書きます。
     共に、THENに指定した結果を出力します。どの条件にも合致しない場合は、ELSE に続いて記述した結果が出力されます。


シンプルケースの例

SELECT
  o.ID,
  o.Description,
  CASE o.Status
    WHEN 1 THEN 'confirmed'
    WHEN 2 THEN 'in production'
    WHEN 3 THEN 'ready'
    WHEN 4 THEN 'shipped'
    ELSE 'unknown status ''' || o.Status || ''''
  END
  FROM Orders o
;


サーチドケースの例

SELECT
  o.ID,
  o.Description,
  CASE
    WHEN (o.Status IS NULL) THEN 'new'
    WHEN (o.Status = 1) THEN 'confirmed'
    WHEN (o.Status = 3) THEN 'in production'
    WHEN (o.Status = 4) THEN 'ready'
    WHEN (o.Status = 5) THEN 'shipped'
    ELSE 'unknown status ''' || o.Status || ''''
  END
  FROM Orders o
;

COALESCE

  • 記述
    <case abbreviation> ::=
        | COALESCE <left paren> <value expression>
            { <comma> <value expression> }... <right paren>
  • 補足説明

     "COALESCE (V1, V2)"のように指定項目が2つの場合は、仕様形で次のように 記述した場合と同等になります。
CASE
  WHEN V1 IS NOT NULL THEN V1
  ELSE V2
END



 "COALESCE (V1, V2,..., Vn)"のように指定項目が3以上の場合は、次のように 記述した場合と同等になります。(ELSE以下は省略してしまっていますが、これは ネストと同じということです。)

CASE
    WHEN V1 IS NOT NULL THEN V1
    ELSE COALESCE (V2,...,Vn)
END
  • 説明
     CASE節の省略形記述です。列の値がに対して、いくつかの式で計算した結果を 返すように指定します。(NULL以外の最初の式が出力結果となります)

     例:
    SELECT
      PROJ_NAME AS Projectname,
      COALESCE(e.FULL_NAME,'[> not assigned <]') AS Employeename
      FROM
        PROJECT p
        LEFT JOIN EMPLOYEE e ON (e.EMP_NO = p.TEAM_LEADER)
    ;


SELECT
  COALESCE(Phone,MobilePhone,'Unknown') AS "Phonenumber"
  FROM
    Relations
;


NULLIF

  • 記述
<case abbreviation> ::=
  NULLIF <left paren> <value expression>
         <comma> <value expression> <right paren>
  • 補足説明

     "NULLIF (V1, V2)"と記述した場合、仕様形で次のように記述した場合と同等に なります。
CASE
  WHEN V1 = V2 THEN NULL
  ELSE V1
END


  • 説明
     指定した式1が式2と同じ値の場合は、結果としてNULLを返します。それ以外の 場合は式1の値をそのまま返します。特定の列が特定の値の時にはNULLに変更して 出力したい場合に使用出来ます。

明示的行ロック

 SELECTのオプションとして追加された"WITH LOCK"の指定により、選択された 行を明示的にロックできます。(実験などで?)ロックを簡易に実現したい場合や、 アプリケーションで正確かつ慎重に制御できる場合の使用を前提として、この 悲観的な(pessimistic)ロック機能は用意されました。
※Firebirdでは、正しく設計されている場合このように悲観的なロックの必要性は非常にまれなことです。 この拡張機能を使用を考える前に、行われる処理の内容をよく理解してください。


  • 記述

    SELECT ... FROM <sometable>
      [WHERE ...]
      [FOR UPDATE [OF ...]]
      [WITH LOCK]
    ...;

  • 説明
     WITH LOCK 節の処理が成功した場合、選択された行に対してロックを行い、 それが現在のトランザクションが終了するまで継続します。ロック中は、他の トランザクションからのライトアクセスは禁止されます。
     もし、FOR UPDATE 節も同時に指定されている場合は、サーバ側の行キャッシュ に各行がロック中であることが順次設定されます。
     別のトランザクションがロックされている行を取得しようと試みた場合、要求時 には成功しているように見えたロック処理は、その時点で失敗となります。
     あなたのアプリケーションで明示的ロックの使用を試みる前に、トランザクション のアイソレーションレベルや他のアトリビュート設定に関して、理解することが 非常に重要です。

     "SELECT ... WITH LOCK"は、DSQL及びPSQLで使用可能です。
     この指定は、最上位レベルかつ1テーブルに対するSELECTの場合でのみ使用 出来ます。サブクエリや、結合セットでは使用できません。また、DISTINCTや GROUP BY、及びその他のオペレーションと同時には指定できません。
     また、ビュー、外部テーブル、セレクトと同じ機能を持ったストアドプロシージャ の出力に対しても使用できません。

     WITH LOCK節を使う時には、以下のことを理解してください。
     データベースエンジンとしては、明示的なロックはレコード単位で処理されます。 データベースステートメントを処理するときには、常に現在コミットされている レコード、または例外を返します。

     待ち動作、及びコンフリクトレポートは、TPBブロックで指定された トランザクションパラメータによって変化します。
    TPBモード動作
    isc_tpb_consistency明示的ロックは、暗黙もしくは明示的なテーブルロックによってオーバライドされ、無視されます。
    isc_tpb_concurrency
    +isc_tpb_nowait
    明示的ロックを始めたトランザクションや他の活発なトランザクションによってレコードの変更が生じた場合、最新コンフリクトレポート例外が発生します。
    isc_tpb_concurrency
    +isc_tpb_wait
    ・レコードがいずれかのトランザクションによりレコードの変更が生じた場合、最新コンフリクトレポート例外が発生します。
    ・活動中のトランザクションが該当レコードの所有権(通常の書込みや明示的ロック)をすでに持っている場合、明示的ロックを試みるトランザクションは、ブロックしてるトランザクションの完了を待ち、ロックを行おうとします。
    ・ブロックしているトランザクションが該当レコードに対し変更を行った場合、やはり最新コンフリクト例外が発生します。
    isc_tpb_read_committed
    +isc_tpb_nowait
    該当するレコードの所有権(通常の書込みや明示的ロック)を持った活動中のトランザクションがあるならば、コンフリクトレポート例外が発生します。
    isc_tpb_read_committed
    +isc_tpb_wait
    ・活動中のトランザクションが該当レコードの所有権をすでに持っている場合、明示的ロックを試みるトランザクションは、ブロックしてるトランザクションの完了を待ち、ロックを行おうとします。
    ・このTPBモードでは、最新コンフリクトレポート例外は発生しません。

 別トランザクションのUPDATE文によってロックされたレコードをアクセスしよう とした場合、最新コンフリクトレポート例外、もしくはロックを行っている トランザクションの終了待ちのどちらかの状態になります。どちらの状態になるか は、TPBモードの設定によって決まります。

 明示的ロックにより返されたレコードに対してロックが行われていることが保証 されるのは、検索条件が他のテーブル、結合、サブクエリおよび他の条件に依存 しない限り、WHERE節で指定された探索条件に合致するものとなります。逆に、 検索条件に合致しないものに対してはロックが行われないことを保証されます。
 つまり、検索された全ての行に対してロックが行われるとは限らないということ です。
 並行する他のトランザクションが変更を伴うコミットを行ってしまった場合、 それは発生します。

 データベースエンジンは、行を取得すると同時にロックを行います。
 数行程度に対してロックを行った場合、これは非常に重要な結果をもたらす 場合があります。
 データベースアクセスのための多くのアクセスでは、自動的に数100程度の行が 含まれたパケットでデータを取得します。ほとんどのデータアクセスコンポーネント では、あるタイミングで取得したパケットにロックされた行が含まれていた場合、 そこで初めてエラーが発生し、さらにそのパケットが最後に取得したパケット となります。
 (訳注:取得の最初は成功していても、途中で急にエラーが発生し、更に取得が 中途半端な所(エラーが出たパケットまで)で終わることが多いということ??)

 FOR UPDATE 節は、オプションの OF <列名> と組み合わせることにより、バッファ からの取得が原因となる最新版取得の問題を防ぎます。それは、使用する アクセスコンポーネントの取得バッファサイズを1に設定することによっても可能 となるかもしれません。
 これらの方法によって、次行の取得とロックの処理を行う前に、現在ロックした 行をコミットするかエラーを検知した場合はロールバックすることが出来ます。

 暗黙的・明示的なセーブポイントをロールバックすると、そのセーブポイント 以降に行われたレコードロックは解放されます。しかし、それは処理を待っている トランザクションには通知されません。
 これは将来的に変えられる可能性があるので、アプリケーションはこの 動作に依存した処理を行うべきではありません。
 (訳注;SQL-99準拠のセーブポイント機能は、1.5で追加されました)


 明示的行ロックは、異常な更新コンフリクトエラーを防ぐために使用することが 出来ますが、もしもあなたがロック処理を綿密な設計と厳密な制御を行わずに 使用すれば、デッドロック発生の危険性は飛躍的に増大します。
 ほとんどの場合において、(Firebirdでは)明示的行ロックは全く必要としません。
 明示的行ロックの主要な目的は2つあります。

  • 非常に処理が重いアプリケーションで、更新コンフリクトエラーを重大な処理では なく軽い方法で処理するため。
  • クラスター環境のリレーショナルデータベースにおいて、複製されたオブジェクト での完全性を容易に維持するため。

 明示的行ロックを使用したいとする理由が以上2つのどちらにも該当しない場合、 Firebirdの使用方法が間違っていると思われます。

 明示的行ロックは非常に便利な機能ですが、誤用しないことが重要です!
 これが重要と考えられる応用分野としては、数千ものライト更新がまさに同時に 行われるような超巨大ウェブサイトや、大企業のERP/CRMシステムがあります。
 しかし、ほとんど大部分のアプリケーションプログラムは、そのような状態 使用されることはまずありません。


例:

  • 単純な使用
    SELECT * FROM DOCUMENT WHERE ID=? WITH LOCK

  • 複数行(DSQLのカーソル向け)
    SELECT * FROM DOCUMENT WHERE PARENT_ID=? FOR UPDATE WITH LOCK

強化された GROUP BY の機能

 1.5で機能が強化されました。

  • 構文
SELECT ... FROM .... [GROUP BY group_by_list]
group_by_list : group_by_item [, group_by_list];
group_by_item : column_name
    | degree (ordinal)
    | udf
    | group_by_function
;
group_by_function : numeric_value_function
    | string_value_function
    | case_expression
;
numeric_value_function :
    EXTRACT '(' timestamp_part FROM value ')';
string_value_function :
    SUBSTRING '(' value FROM pos_short_integer ')'
    | SUBSTRING '(' value FROM pos_short_integer FOR
        nonneg_short_integer ')'
    | KW_UPPER '(' value ')'
;

 group_by_item の中には、式の中であろうとも集計関数の類を含めてはいけません。

  • HAVING
     GROUP BY 節で指定したものが、HAVING節の集計関数あるいは有効な式で使用 出来ます。以前は、GROUP BY で指定されていないものや非有効な式を用いること が許容されていました。
  • ORDER BY
     コンテキストが集計用の場合、ORDER BY節またはGROUP BY節で指定したもののみが、 集計関数の有効な式に使用できます。以前は、正当でないものを用いることが出来 ました。
  • サブクエリ中の集計関数
     サブクエリ中の集計関数や表現で、GROUP BY節で指定されているものも使用できます。
SELECT
  r.RDB$RELATION_NAME,
  MAX(r.RDB$FIELD_POSITION),
  (SELECT
      r2.RDB$FIELD_NAME
    FROM
      RDB$RELATION_FIELDS r2
    WHERE
      r2.RDB$RELATION_NAME = r.RDB$RELATION_NAME and
      r2.RDB$FIELD_POSITION = MAX(r.RDB$FIELD_POSITION)
  )
  FROM
    RDB$RELATION_FIELDS r
  GROUP BY
    1
SELECT
  rf.RDB$RELATION_NAME AS "Relationname",
  (SELECT
      r.RDB$RELATION_ID
    FROM
      RDB$RELATIONS r
    WHERE
      r.RDB$RELATION_NAME = rf.RDB$RELATION_NAME) AS "ID",
      COUNT(*) AS "Fields"
  )
  FROM
    RDB$RELATION_FIELDS rf
  GROUP BY
    rf.RDB$RELATION_NAME
  • 別コンテキストの集計関数の使用
     別コンテキストの集計関数も、式の中で使用できます。
SELECT
  r.RDB$RELATION_NAME,
  MAX(i.RDB$STATISTICS) AS "Max1",
  (SELECT
      COUNT(*) || ' - ' || MAX(i.RDB$STATISTICS)
    FROM
      RDB$RELATION_FIELDS rf
    WHERE
      rf.RDB$RELATION_NAME = r.RDB$RELATION_NAME) AS "Max2"
    FROM
      RDB$RELATIONS r
      JOIN
        RDB$INDICES i on (i.RDB$RELATION_NAME = r.RDB$RELATION_NAME)
  )
  GROUP BY
    r.RDB$RELATION_NAME
  HAVING
    MIN(i.RDB$STATISTICS) <> MAX(i.RDB$STATISTICS)

 ※注意!
    このクエリはFB1.0でも動作しますが、結果は間違ったものとなります。

  • 集計関数でのサブクエリ
     集計関数でのサブクエリは、単行SELECTのみ許されます。
SELECT
    r.RDB$RELATION_NAME,
    SUM(
      (SELECT
          COUNT(*)
        FROM
          RDB$RELATION_FIELDS rf
        WHERE
          rf.RDB$RELATION_NAME = r.RDB$RELATION_NAME
      )
    )
  FROM
    RDB$RELATIONS r
    JOIN RDB$INDICES i on (i.RDB$RELATION_NAME = r.RDB$RELATION_NAME)
  GROUP BY
    r.RDB$RELATION_NAME
  • 集計関数のネスト
     集計関数がサブクエリなどのより低いレベルのコンテキスト内で使用されている 場合は、集計関数のなかで集計関数を使用することが可能です。 上の例を見てください。
  • 順数(degree)指定によるグループ化
     GROUP BY 節の指定には、出力列リストでの順数も使用できます。この場合は、 相当する出力項目をコピーした場合と同等になります。 その順数が示す項目がサブクエリがあるものを指し示す場合には、その同じ サブクエリが最低2度は実行されることとなります。

ORDER BY でNULLの扱いを設定

 1.5で機能が強化されました。
 ORDER BY 節は、クエリ結果をソートするための正当な式を指定します。
 その式が1つの数値のみである場合、以前と同様にそれは出力列リストの順数と 解釈されます。そこにnullsを指定した場合、結果セット(の出力順)は、nulls配置節 を用いて制御されます。
 "NULLS FIRST"が指定された場合はnullが最上方に、"NULLS LAST"が指定された 場合は最下方に配置されます。指定を行わない場合は、"NULLS LAST"を指定した場合 と同様になります。

  • 構文
SELECT ... FROM .... [ORDER BY order_list]....;
order_list : order_item [, order_list];
order_item : <expression> [order_direction] [nulls_placement]
order_direction : ASC | DESC;
nulls_placement : NULLS FIRST | NULLS LAST;
  • 制限事項
    • NULL FIRST が指定されると、インデックスがソートに使用されなくなります。
    • UDFやストアドプロシージャなどから返される値が論理ソートで使用できない ものであった場合、結果は予測不能です。
    • UDFやストアドプロシージャの項目に対してこの指定を行った場合、それが たとえ順数指定であったとしても、結果は全く予測不能です。
    • UNIONクエリの出力をソートする場合は、出力列リストの順数指定のみが 可能です。
SELECT * FROM MSG
ORDER BY PROCESS_TIME DESC NULLS FIRST
SELECT FIRST 10 * FROM DOCUMENT
ORDER BY STRLEN(DESCRIPTION) DESC
SELECT DOC_NUMBER, DOC_DATE FROM PAYORDER
UNION ALL
SELECT DOC_NUMBER, DOC_DATA FROM BUDGORDER
ORDER BY 2 DESC NULLS LAST, 1 ASC NULLS FIRST

参照

DECLARE CURSOR , DELETE , INSERT , UPDATE