Table1
最終更新日 :
2025年3月4日
SQL やデータベース操作に慣れている方にはお馴染みのクエリオブクエリ機能もいくつか含まれています。
ドット表記法の使用
クエリオブクエリでは、テーブル名のドット表記法がサポートされています。
例
A という構造体に B というフィールドが含まれており、その中に Products というテーブルが含まれている場合は、次のようなドット表記法でこのテーブルを参照できます。
FROM A.B.Products;
FROM A.B.Products;
FROM A.B.Products;
結合の使用
結合操作では、1 つの SELECT ステートメントを使用して、複数の関連するテーブルから 1 つの結果セットを返します。通常、それらのテーブルは、プライマリキーと外部キーの関係で関連付けられています。結合を実行する SQL 節は 2 つあります。
- WHERE 節：クエリオブクエリでは、WHERE 節による 結合がサポートされています。
- INNER JOIN および OUTER JOIN：クエリオブクエリでは、INNER JOIN または OUTER JOIN 節による結合はサポートされていません。
注意：
クエリオブクエリでサポートされているのは、2 つのテーブルの結合のみです。
UNION の使用
UNION 演算子を使用して、複数の SELECT 式の結果を 1 つのレコードセットに結合できます。結合するテーブルの列数が同じであり、対応する列のデータ型が UNION と互換性がある必要があります。次のいずれかの条件に該当する場合、列のデータ型は UNION と互換性があります。
- 同じデータ型である場合。例えば、両方とも Tinyint である場合。
- 両方ともが数値である場合。例えば、Tinyint、Smallint、Integer、Bigint、Double、Float、Real、Decimal、Numeric など。
- 両方とも文字である場合。例えば、Char、Varchar、LongVarchar など。
- 両方とも日付である場合。例えば、Time、TimeStamp、Date など。
注意：
クエリオブクエリでは、ODBC 形式の日付および時刻はサポートされていません。
シンタックス
select_expression = select_expression UNION [ALL] select_expression
select_expression = select_expression UNION [ALL] select_expression
select_expression = select_expression UNION [ALL] select_expression
例
次のテーブルがあるとします。
|
|
|
|---|---|
|
Type(int) |
Name(varchar) |
|
1 |
テニス |
|
2 |
野球 |
|
3 |
サッカー |
|
Table2 |
|
|---|---|
|
ID(int) |
Sport(varchar) |
|
3 |
サッカー |
|
4 |
バレーボール |
|
5 |
卓球 |
Table1 と Table2 を結合するには、次のように UNION ステートメントを使用します。
UNION |
この UNION ステートメントによって、次の結果（UNION）テーブルが作成されます。
|
結果テーブル |
|
|---|---|
|
Type(int) |
Name(varchar) |
|
1 |
テニス |
|
2 |
野球 |
|
3 |
サッカー |
|
4 |
バレーボール |
|
5 |
卓球 |
列名のエイリアスの使用
UNION テーブルでの列名は、UNION 操作の最初の SELECT ステートメントの結果セットの列名になり、もう一方の SELECT ステートメントの列名は無視されます。結果テーブルの列名を変更するには、次のようにエイリアスを使用します。
UNION |
行の重複と複数のテーブル
デフォルトでは、UNION 演算子によって結果テーブルから重複している行が削除されます。キーワード ALL を使用すると、重複している行が含まれるようになります。
次の例のように、UNION 演算子を使用すると無制限にテーブルを結合できます。
UNION |
括弧と評価の順序
デフォルトでは、クエリオブクエリの SQL エンジンは、UNION 演算子を含んでいるステートメントを左から右へ評価します。評価順序を変更するには、括弧を使用します。例えば、次の 2 つのステートメントは異なります。
/* 最初のステートメント。*/
SELECT * FROM TableA
UNION ALL
(SELECT * FROM TableB
UNION
SELECT * FROM TableC
)
/* 2 番目のステートメント。*/
(SELECT * FROM TableA
UNION ALL
SELECT * FROM TableB
)
UNION
SELECT * FROM TableC
/* 最初のステートメント。*/
SELECT * FROM TableA
UNION ALL
(SELECT * FROM TableB
UNION
SELECT * FROM TableC
)
/* 2 番目のステートメント。*/
(SELECT * FROM TableA
UNION ALL
SELECT * FROM TableB
)
UNION
SELECT * FROM TableC
/* 最初のステートメント。*/ SELECT * FROM TableA UNION ALL (SELECT * FROM TableB UNION SELECT * FROM TableC ) /* 2 番目のステートメント。*/ (SELECT * FROM TableA UNION ALL SELECT * FROM TableB ) UNION SELECT * FROM TableC
ステートメント 1 では、TableB と TableC の結合で重複が除外されます。次に、このセットと TableA の結合で、キーワード ALL によって重複が保持されます。ステートメント 2 では、TableA と TableB の結合で重複が保持されますが、後続の TableC との結合で重複が除外されます。結果として、キーワード ALL の効果はなくなっています。
UNION での他のキーワードの使用
UNION を実行する場合、個々の SELECT ステートメントで ORDER BY 節または COMPUTE 節を指定することはできません。最後の SELECT ステートメントの後ろに ORDER BY または COMPUTE 節を 1 つだけ入れることができます。この節は、結合された最後の結果セットに適用されます。それぞれの SELECT ステートメントには、GROUP BY および HAVING の式のみを指定できます。
条件演算子の使用
クエリオブクエリでは、SQL ステートメントで次の条件演算子を使用できます。
テスト条件演算子
この条件演算子は、ブール式が True、False、または Unknown のいずれであるかをテストします。
シンタックス
cond_test ::= expression [IS [NOT] {TRUE | FALSE | UNKNOWN} ]
cond_test ::= expression [IS [NOT] {TRUE | FALSE | UNKNOWN} ]
cond_test ::= expression [IS [NOT] {TRUE | FALSE | UNKNOWN} ]
例
WHERE _isValid IS true;
WHERE _isValid IS true;
WHERE _isValid IS true;
null 条件演算子
この条件演算子は、式が null かどうかをテストします。
シンタックス
null_cond ::= expression IS [NOT] NULL
null_cond ::= expression IS [NOT] NULL
null_cond ::= expression IS [NOT] NULL
例
WHERE bloodVal IS NOT null;
WHERE bloodVal IS NOT null;
WHERE bloodVal IS NOT null;
比較条件演算子
この条件演算子は、ある式と、それと同じデータ型（数値、文字列、日付、またはブール値）を持つ別の式を比較します。レコードセットの必要な行のみを選択的に取得する場合に使用できます。
シンタックス
comparison_cond ::= expression [= | > | >= | <> | != | < | <=] expression
comparison_cond ::= expression [= | > | >= | <> | != | < | <=] expression
comparison_cond ::= expression [= | > | >= | <> | != | < | <=] expression
例
次の例では、比較条件演算子を使用して、IQ が 150 以上の犬のみを取得します。
FROM Dogs
WHERE dog_IQ >= 150;
FROM Dogs
WHERE dog_IQ >= 150;
FROM Dogs WHERE dog_IQ >= 150;
BETWEEN 条件演算子
この条件演算子は、式と式を比較します。レコードセットの必要な行のみを選択的に取得する場合に使用できます。比較条件演算子と同様に、BETWEEN 条件演算子も比較を行います。ただし、BETWEEN 条件演算子は、特定の範囲の値との比較を行います。したがって、そのシンタックスには、範囲を表す最小値と最大値の 2 つの値が必要です（それらの値も範囲に含まれます）。これらの値はキーワード AND で区切ります。
シンタックス
between_cond ::= expression [NOT] BETWEEN expression AND expression
between_cond ::= expression [NOT] BETWEEN expression AND expression
between_cond ::= expression [NOT] BETWEEN expression AND expression
例
次の例では、BETWEEN 条件演算子を使用して、IQ が 150 以上 165 以下の犬のみを取得します。
FROM Dogs
WHERE dog_IQ BETWEEN 150 AND 165;
FROM Dogs
WHERE dog_IQ BETWEEN 150 AND 165;
FROM Dogs WHERE dog_IQ BETWEEN 150 AND 165;
IN 条件演算子
この条件演算子を使用すると、一致条件のコンマ区切りのリストを指定できます。これは、OR 条件演算子と似ています。IN 条件演算子は、長いリストを使用する場合にコードが読みやすくなるだけでなく、別の SELECT ステートメントを含めることもできます。
シンタックス
in_cond ::= expression [NOT] IN (expression_list)
in_cond ::= expression [NOT] IN (expression_list)
in_cond ::= expression [NOT] IN (expression_list)
例
次の例では、IN 条件演算子を使用して、Ken's Kennels または Barb's Breeders のどちらかで誕生した犬のみを取得します。
FROM Dogs
WHERE kennel_ID IN ('Kens','Barbs');
FROM Dogs
WHERE kennel_ID IN ('Kens','Barbs');
FROM Dogs
WHERE kennel_ID IN ('Kens','Barbs');
LIKE 条件演算子
この条件演算子を使用すると、検索パターンとデータを比較する、ワイルドカード検索を実行できます。LIKE 条件演算子は、データを部分的に不明な値と比較する点で、BETWEEN、IN などの他の条件演算子とは異なります。
シンタックス
like_cond ::= left_string_exp [NOT] LIKE right_string_exp [ESCAPE escape_char]
like_cond ::= left_string_exp [NOT] LIKE right_string_exp [ESCAPE escape_char]
like_cond ::= left_string_exp [NOT] LIKE right_string_exp [ESCAPE escape_char]
left_string_exp には、特定の文字列か、文字列の列への参照を指定できます。right_string_exp には、文字列の列への参照か、検索パターンを指定できます。検索パターンは、リテラルテキストと少なくとも 1 つのワイルドカード文字で構成される検索条件です。ワイルドカード文字は、検索パターンの不明な部分を表す特殊文字で、次のように解釈されます。
- アンダースコア（_）は任意の 1 文字を表します。
- パーセント記号（%）は 0 個以上の文字を表します。
- 角括弧（[ ]）はその範囲内の任意の文字を表します。
- キャレットが入っている角括弧は、その範囲にない任意の文字を表します。
他のすべての文字は、その文字自体を表します。
注意：
以前のバージョンの ColdFusion では、括弧で囲まれた範囲はサポートされていません。
例
次の例では、LIKE 条件演算子を使用して、Boston Terrier、Jack Russell Terrier、Scottish Terrier などの、テリア種の犬のみを取得します。
FROM Dogs
WHERE breed LIKE '%Terrier';
FROM Dogs
WHERE breed LIKE '%Terrier';
FROM Dogs WHERE breed LIKE '%Terrier';
次の例は、括弧で囲まれた範囲を使用する SELECT ステートメントです。
SELECT lname FROM Suspects WHERE lname LIKE '[a-m]%';
SELECT lname FROM Suspects WHERE lname LIKE '%[]';
SELECT lname FROM Suspects WHERE lname LIKE 'A[%]%';
SELECT lname FROM Suspects WHERE lname LIKE 'A[^c-f]%';
SELECT lname FROM Suspects WHERE lname LIKE '[a-m]%';
SELECT lname FROM Suspects WHERE lname LIKE '%[]';
SELECT lname FROM Suspects WHERE lname LIKE 'A[%]%';
SELECT lname FROM Suspects WHERE lname LIKE 'A[^c-f]%';
SELECT lname FROM Suspects WHERE lname LIKE '[a-m]%'; SELECT lname FROM Suspects WHERE lname LIKE '%[]'; SELECT lname FROM Suspects WHERE lname LIKE 'A[%]%'; SELECT lname FROM Suspects WHERE lname LIKE 'A[^c-f]%';
大文字と小文字の区別
ColdFusion の通常のルールとは異なり、クエリオブクエリでは大文字と小文字が区別されます。ただし、クエリオブクエリでは 2 つの文字列関数 UPPER() と LOWER() がサポートされているので、これらを使用すれば大文字と小文字を区別せずに検索を行えます。
例
次の例では、'Sylvester' のみが検索されます。
FROM Dogs
WHERE dog_name LIKE 'Sylvester';
FROM Dogs
WHERE dog_name LIKE 'Sylvester';
FROM Dogs WHERE dog_name LIKE 'Sylvester';
次の例では大文字と小文字が区別されません。LOWER() 関数を使用して、'Sylvester'、'sylvester'、'SYLVESTER' などをすべて小文字に変換したうえで、すべて小文字の文字列 'sylvester' と比較します。
FROM Dogs
WHERE LOWER(dog_name) LIKE 'sylvester';
FROM Dogs
WHERE LOWER(dog_name) LIKE 'sylvester';
FROM Dogs WHERE LOWER(dog_name) LIKE 'sylvester';
LIKE 条件の右側に変数を使用している場合に、大文字と小文字を区別せずに比較するには、LCase 関数または UCase 関数を使用して、変数のすべてのテキストを大文字または小文字に揃えます。次に例を示します。
WHERE LOWER(dog_name) LIKE '#LCase(FORM.SearchString)#';
WHERE LOWER(dog_name) LIKE '#LCase(FORM.SearchString)#';
WHERE LOWER(dog_name) LIKE '#LCase(FORM.SearchString)#';
ワイルドカードのエスケープ
条件演算子の ESCAPE 節を使用して、独自のエスケープ文字を指定できます。
例
FROM Benefits
WHERE emp_discount LIKE '10\%'
ESCAPE '\';
FROM Benefits
WHERE emp_discount LIKE '10\%'
ESCAPE '\';
FROM Benefits WHERE emp_discount LIKE '10\%' ESCAPE '\';
列のデータ型の管理
クエリオブクエリでは、列のデータ型を定義するメタデータが各列に設定されている必要があります。ColdFusion で作成されるすべてのクエリにはメタデータが設定されます。ただし、QueryNew 関数で 2 番目のパラメーターを省略した場合、作成されるクエリにはメタデータが含まれていません。このオプションの 2 番目のパラメーターを使用して、クエリの各列のデータ型を定義します。
QueryNew 関数での列のデータ型の指定
- QueryNew 関数を入力します。次の例に示すとおり、最初のパラメーターには列名を、2 番目のパラメーターにはデータ型を指定します。
<cfset qInstruments = queryNew("name, instrument, years_playing", "CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_INTEGER")>
<cfset qInstruments = queryNew("name, instrument, years_playing", "CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_INTEGER")>
<cfset qInstruments = queryNew("name, instrument, years_playing", "CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_INTEGER")>
注意：
クエリオブクエリのメタデータを確認するには、GetMetaData 関数を使用します。
QueryAddColumn 関数での列のデータ型の指定
-
パラメーターを設定していない QueryNew 関数を指定して、クエリを作成します。
<cfset myQuery = QueryNew("")><cfset myQuery = QueryNew("")><cfset myQuery = QueryNew("")>
-
QueryAddColumn 関数を使用して列を追加します。3 番目のパラメータでデータ型を指定します。
<cfset FastFoodArray = ArrayNew(1)><cfset FastFoodArray[1] = "French Fries"><cfset FastFoodArray[2] = "Hot Dogs"><cfset FastFoodArray[3] = "Fried Clams"><cfset FastFoodArray[4] = "Thick Shakes"><!--- この配列を使用してクエリに列を追加する。 ---><cfset nColumnNumber = QueryAddColumn(myQuery, "FastFood", "CF_SQL_VARCHAR",FastFoodArray)><cfset FastFoodArray = ArrayNew(1)> <cfset FastFoodArray[1] = "French Fries"> <cfset FastFoodArray[2] = "Hot Dogs"> <cfset FastFoodArray[3] = "Fried Clams"> <cfset FastFoodArray[4] = "Thick Shakes"> <!--- この配列を使用してクエリに列を追加する。 ---> <cfset nColumnNumber = QueryAddColumn(myQuery, "FastFood", "CF_SQL_VARCHAR", FastFoodArray)><cfset FastFoodArray = ArrayNew(1)> <cfset FastFoodArray[1] = "French Fries"> <cfset FastFoodArray[2] = "Hot Dogs"> <cfset FastFoodArray[3] = "Fried Clams"> <cfset FastFoodArray[4] = "Thick Shakes"> <!--- この配列を使用してクエリに列を追加する。 ---> <cfset nColumnNumber = QueryAddColumn(myQuery, "FastFood", "CF_SQL_VARCHAR", FastFoodArray)>
データ型を指定しなかった場合は、条件式の実行時に各列の最初の 50 行がチェックされて、データ型が推定されます。
場合によっては、推定されたデータ型がアプリケーションで適切に機能しないことがあります。特に、WHERE 節や他の条件式でその列を使用している場合は、データ型が互換性を持っている必要があります。互換性がない場合は、CAST 関数を使用して、互換性のあるデータ型にいずれかの列を再キャストします。キャストの詳細については、「CAST 関数の使用」を参照してください。
データ型の互換性について詳しくは、クエリオブクエリの処理についてを参照してください。
注意：
QueryNew 関数でデータ型を指定すると、互換性に関する問題を回避できます。
CAST 関数の使用
場合によっては、列のデータ型が、実行したい処理に対して互換性のあるデータ型でないことがあります。例えば、cfhttp タグによって返されるクエリ列は、すべて数値が含まれている場合でも、必ず CF_SQL_VARCHAR 型になります。この場合は、クエリオブクエリの CAST 関数を使用して、列の値を適切なデータ型に変換します。
CAST 関数のシンタックスは次のとおりです。
CAST ( expression AS castType )
CAST ( expression AS castType )
CAST ( expression AS castType )
ここで、 castType は次のいずれかです。
- BINARY
- BIGINIT
- BIT
- DATE
- DECIMAL
- DOUBLE
- INTEGER
- TIME
- TIMESTAMP
- VARCHAR
例えば、次のように使用します。
<cfhttp url="http://quote.yahoo.com/download/quotes.csv?Symbols=csco,jnpr&format=sc1l1&ext=.csv"
method="GET"
name="qStockItems"
columns="Symbol,Change,LastTradedPrice"
textqualifier=""""
delimiter=","
firstrowasheaders="no">
<cfoutput>
<cfdump var="#qStockItems#">
<cfdump var="#qStockItems.getColumnNames()#">
</cfoutput>
<cfoutput>
<cfloop index="i" from="1" to="#arrayLen(qStockItems.getColumnNames())#">
#qStockItems.getMetaData().getColumnTypeName(javaCast("int",i))#<br/>
</cfloop>
</cfoutput>
<cftry>
<cfquery name="hello" dbtype="query">
SELECT SUM(CAST(qStockItems.LastTradedPrice as INTEGER))
AS SUMNOW from qStockItems
</cfquery>
<cfcatch>Error in Query of Queries</cfcatch>
</cftry>
<cfoutput>
<cfdump var="#hello#">
</cfoutput>
<cfhttp url="http://quote.yahoo.com/download/quotes.csv?Symbols=csco,jnpr&format=sc1l1&ext=.csv"
method="GET"
name="qStockItems"
columns="Symbol,Change,LastTradedPrice"
textqualifier=""""
delimiter=","
firstrowasheaders="no">
<cfoutput>
<cfdump var="#qStockItems#">
<cfdump var="#qStockItems.getColumnNames()#">
</cfoutput>
<cfoutput>
<cfloop index="i" from="1" to="#arrayLen(qStockItems.getColumnNames())#">
#qStockItems.getMetaData().getColumnTypeName(javaCast("int",i))#<br/>
</cfloop>
</cfoutput>
<cftry>
<cfquery name="hello" dbtype="query">
SELECT SUM(CAST(qStockItems.LastTradedPrice as INTEGER))
AS SUMNOW from qStockItems
</cfquery>
<cfcatch>Error in Query of Queries</cfcatch>
</cftry>
<cfoutput>
<cfdump var="#hello#">
</cfoutput>
<cfhttp url="http://quote.yahoo.com/download/quotes.csv?Symbols=csco,jnpr&format=sc1l1&ext=.csv" method="GET" name="qStockItems" columns="Symbol,Change,LastTradedPrice" textqualifier="""" delimiter="," firstrowasheaders="no"> <cfoutput> <cfdump var="#qStockItems#"> <cfdump var="#qStockItems.getColumnNames()#"> </cfoutput> <cfoutput> <cfloop index="i" from="1" to="#arrayLen(qStockItems.getColumnNames())#"> #qStockItems.getMetaData().getColumnTypeName(javaCast("int",i))#<br/> </cfloop> </cfoutput> <cftry> <cfquery name="hello" dbtype="query"> SELECT SUM(CAST(qStockItems.LastTradedPrice as INTEGER)) AS SUMNOW from qStockItems </cfquery> <cfcatch>Error in Query of Queries</cfcatch> </cftry> <cfoutput> <cfdump var="#hello#"> </cfoutput>
集計関数の使用
集計関数は、データのセットを処理して 1 つの値を返します。この関数は、テーブル全体を取得してからテーブル全体のレコードセットを処理するのとは対照的に、テーブルから要約情報を取得します。
次の操作を実行する場合は、集計関数の使用を検討してください。
- 列の平均を表示する
- 列の行数を数える
- 列内で最も早い日付を検出する
すべてのリレーショナルデータベース管理システム（RDBMS）ですべての集計関数がサポートされているわけではありません。詳しくは、データベースのマニュアルを参照してください。次の表に、クエリオブクエリでサポートされている集計関数をリストします。
|
関数 |
説明 |
|---|---|
|
AVG() |
列の平均を返します。 |
|
COUNT() |
列の行数を返します。 |
|
MAX() |
列の最大値を返します。 |
|
MIN() |
列の最小値を返します。 |
|
SUM() |
列の合計値を返します。 |
シンタックス
([ALL | DISTINCT] numeric_exp)
([ALL | DISTINCT] numeric_exp)
([ALL | DISTINCT] numeric_exp)
例
次の例では、AVG() 関数を使用してすべてのテリアの IQ の平均を取得します。
FROM Dogs
WHERE breed LIKE '%Terrier';
FROM Dogs
WHERE breed LIKE '%Terrier';
FROM Dogs WHERE breed LIKE '%Terrier';
任意の式への集計関数の適用
クエリオブクエリでは次のように、任意の式に対して集計関数を適用できます。
FROM roysched
GROUP BY lorange;
FROM roysched
GROUP BY lorange;
FROM roysched GROUP BY lorange;
集計関数を含む式
クエリオブクエリでは次のように、集計関数を含む数式がサポートされています。
FROM roysched
GROUP BY lorange;
FROM roysched
GROUP BY lorange;
FROM roysched GROUP BY lorange;
GROUP BY と HAVING 式の使用
クエリオブクエリでは、エイリアスを使用して参照すれば、任意の数式を使用できます。
例
次のコードは、正しいコードです。
SELECT (lorange + hirange)/2 AS midrange,
COUNT(*)
FROM roysched
GROUP BY midrange;
SELECT (lorange + hirange)/2 AS midrange,
COUNT(*)
FROM roysched
GROUP BY midrange;
SELECT (lorange + hirange)/2 AS midrange, COUNT(*) FROM roysched GROUP BY midrange;
次のコードは、正しいコードです。
SELECT (lorange+hirange)/2 AS x,
COUNT(*)
FROM roysched GROUP BY x
HAVING x > 10000;
SELECT (lorange+hirange)/2 AS x,
COUNT(*)
FROM roysched GROUP BY x
HAVING x > 10000;
SELECT (lorange+hirange)/2 AS x, COUNT(*) FROM roysched GROUP BY x HAVING x > 10000;
次のコードは、クエリオブクエリではサポートされません。
SELECT (lorange + hirange)/2 AS midrange,
COUNT(*)
FROM roysched
GROUP BY (lorange + hirange)/2;
SELECT (lorange + hirange)/2 AS midrange,
COUNT(*)
FROM roysched
GROUP BY (lorange + hirange)/2;
SELECT (lorange + hirange)/2 AS midrange, COUNT(*) FROM roysched GROUP BY (lorange + hirange)/2;
ORDER BY 節の使用
クエリオブクエリでは、ORDER BY 節を使用したソートがサポートされています。これは、SELECT ステートメントの最後に配置する必要があります。複数の列、位置で指定した列、非選択の列を基準にしてソートできます。キーワード DESC を使用すると、降順のソートを指定できます（デフォルトでは、多くの RDBMS ソートは昇順なので、キーワード ASC は不要です）。
シンタックス
order_by_column ::= ( <IDENTIFIER> | <INTEGER_LITERAL> ) [<ASC> | <DESC>]
order_by_column ::= ( <IDENTIFIER> | <INTEGER_LITERAL> ) [<ASC> | <DESC>]
order_by_column ::= ( <IDENTIFIER> | <INTEGER_LITERAL> ) [<ASC> | <DESC>]
例
次の例は、ORDER BY 節を使用した簡単なソートです。
FROM results
ORDER BY dopamine_levels
FROM results
ORDER BY dopamine_levels
FROM results ORDER BY dopamine_levels
次の例では、より複雑なソートを行っています。まずドーパミンレベルで昇順にソートしてから、アセチルコリンで降順にソートしています。キーワード ASC は不要です。これは、明示したい場合にのみ使用します。
FROM results
ORDER BY 2 ASC, 1 DESC
FROM results
ORDER BY 2 ASC, 1 DESC
FROM results ORDER BY 2 ASC, 1 DESC
エイリアスの使用
クエリオブクエリでは、データベース列のエイリアスが使用できます。エイリアスとは、データベースフィールドまたは値に割り当てる別名です。エイリアスは、同じ SQL ステートメント内で再利用できます。
エイリアスの使用例としては、複数の列を結合して 1 つの値を生成する場合があります。例えば、 最後 と名を連結して フルネーム .新しい値はデータベース内に存在しないので、エイリアスを割り当てて参照します。SELECT ステートメントでエイリアスを割り当てるには、AS キーワードを使用します。
例
ORDER BY 節、GROUP BY 節、および HAVING 節でエイリアスを使用できます。
注意：
テーブル名にエイリアスを割り当てることはできません。
from Employee;
from Employee;
from Employee;
以降の例は、次の 2 つのプライマリクエリに基づいています。
SELECT * FROM employee
</cfquery>
<cfquery name="roysched" datasource="2pubs">
SELECT * FROM roysched
</cfquery>
SELECT * FROM employee
</cfquery>
<cfquery name="roysched" datasource="2pubs">
SELECT * FROM roysched
</cfquery>
SELECT * FROM employee </cfquery> <cfquery name="roysched" datasource="2pubs"> SELECT * FROM roysched </cfquery>
ORDER BY の例
SELECT (job_id || job_lvl)/2 AS job_value
FROM employee
ORDER BY job_value
</cfquery>
SELECT (job_id || job_lvl)/2 AS job_value
FROM employee
ORDER BY job_value
</cfquery>
SELECT (job_id || job_lvl)/2 AS job_value FROM employee ORDER BY job_value </cfquery>
GROUP BY の例
SELECT lorange || hirange AS x, count(hirange)
FROM roysched
GROUP BY x
</cfquery>
SELECT lorange || hirange AS x, count(hirange)
FROM roysched
GROUP BY x
</cfquery>
SELECT lorange || hirange AS x, count(hirange) FROM roysched GROUP BY x </cfquery>
HAVING の例
SELECT (lorange || hirange)/2 AS x,
COUNT(*)
FROM roysched GROUP BY x
HAVING x > 10000
</cfquery>
SELECT (lorange || hirange)/2 AS x,
COUNT(*)
FROM roysched GROUP BY x
HAVING x > 10000
</cfquery>
SELECT (lorange || hirange)/2 AS x, COUNT(*) FROM roysched GROUP BY x HAVING x > 10000 </cfquery>
null 値の処理
条件式はブール論理（2 値論理）を使用して処理されますが、null 値を正しく処理するには 3 値論理を使用する必要があります。IS NOT NULL 節は、クエリオブクエリで正しく機能します。ただし、次の式は、breed 列が null の場合には正しく機能しません。
WHERE NOT (breed > 'A')
WHERE NOT (breed > 'A')
WHERE NOT (breed > 'A')
正しく動作するには、いずれの式の結果セットにも null の breed 列が含まれていないことが必要です。この制限を避けるには、条件に明示的なルールを追加して、次のように変更します。
WHERE breed IS NOT NULL AND not (breed > 'A')
WHERE breed IS NOT NULL AND not (breed > 'A')
WHERE breed IS NOT NULL AND not (breed > 'A')
文字列の連結
クエリオブクエリでは、次の例のように、+ および || の 2 つの文字列連結演算子をサポートしています。
LASTNAME || ', ' || FIRSTNAME
LASTNAME || ', ' || FIRSTNAME
LASTNAME || ', ' || FIRSTNAME
予約語のエスケープ
ColdFusion には予約語があります。その多くは SQL 言語のキーワードであり、通常は列名やテーブル名として使用しません。列名やテーブル名で予約語をエスケープするには括弧で囲みます。
注意：
以前のバージョンの ColdFusion では、エスケープしなくても一部の予約語が使用できます。
例
次に示す SELECT ステートメントの例は有効です。
SELECT [group].firstname FROM [group];
SELECT [group].[from] FROM [group];
SELECT [group].firstname FROM [group];
SELECT [group].[from] FROM [group];
SELECT [group].firstname FROM [group]; SELECT [group].[from] FROM [group];
次の例のようなネストされているエスケープはサポートされていません。
SELECT [[from]] FROM T;
SELECT [[from]] FROM T;
SELECT [[from]] FROM T;
次の表に、ColdFusion の予約語のリストを示します。
ABSOLUTE |
ACTION |
ADD |
ALL |
ALLOCATE |
|---|---|---|---|---|
ALTER |
AND |
ANY |
ARE |
AS |
ASC |
ASSERTION |
AT |
AUTHORIZATION |
AVG |
BEGIN |
BETWEEN |
BIT |
BIT_LENGTH |
BOTH |
BY |
CASCADE |
CASCADED |
CASE |
CAST |
CATALOG |
CHAR |
CHARACTER |
CHARACTER_LENGTH |
CHAR_LENGTH |
CHECK |
CLOSE |
COALESCE |
COLLATE |
COLLATION |
COLUMN |
COMMIT |
CONNECT |
CONNECTION |
CONSTRAINT |
CONSTRAINTS |
CONTINUE |
CONVERT |
CORRESPONDING |
COUNT |
CREATE |
CROSS |
CURRENT |
CURRENT_DATE |
CURRENT_TIME |
CURRENT_TIMESTAMP |
CURRENT_USER |
CURSOR |
DATE |
DAY |
DEALLOCATE |
DEC |
DECIMAL |
DECLARE |
DEFAULT |
DEFERRABLE |
DEFERRED |
DELETE |
DESC |
DESCRIBE |
DESCRIPTOR |
DIAGNOSTICS |
DISCONNECT |
DISTINCT |
DOMAIN |
DOUBLE |
DROP |
ELSE |
END |
END-EXEC |
ESCAPE |
EXCEPT |
EXCEPTION |
EXEC |
EXECUTE |
EXISTS |
EXTERNAL |
EXTRACT |
FALSE |
FETCH |
FIRST |
FLOAT |
FOR |
FOREIGN |
FOUND |
FROM |
FULL |
GET |
GLOBAL |
GO |
GOTO |
GRANT |
GROUP |
HAVING |
HOUR |
IDENTITY |
IMMEDIATE |
IN |
INDICATOR |
INITIALLY |
INNER |
INPUT |
INSENSITIVE |
INSERT |
INT |
INTEGER |
INTERSECT |
INTERVAL |
INTO |
IS |
ISOLATION |
JOIN |
KEY |
LANGUAGE |
LAST |
LEADING |
LEFT |
LEVEL |
LIKE |
LOCAL |
LOWER |
MATCH |
MAX |
MIN |
MINUTE |
MODULE |
MONTH |
NAMES |
NATIONAL |
NATURAL |
NCHAR |
NEXT |
NO |
NOT |
NULL |
NULLIF |
NUMERIC |
OCTET_LENGTH |
OF |
ON |
ONLY |
OPEN |
OPTION |
または |
ORDER |
OUTER |
OUTPUT |
OVERLAPS |
PAD |
PARTIAL |
POSITION |
PRECISION |
PREPARE |
PRESERVE |
PRIMARY |
PRIOR |
PRIVILEGES |
PROCEDURE |
PUBLIC |
READ |
REAL |
REFERENCES |
RELATIVE |
RESTRICT |
REVOKE |
RIGHT |
ROLLBACK |
ROWS |
SCHEMA |
SCROLL |
SECOND |
SECTION |
SELECT |
SESSION |
SESSION_USER |
SET |
SMALLINT |
SOME |
SPACE |
|
SQL |
SQLCODE |
SQLERROR |
SQLSTATE |
SUBSTRING |
SUM |
SYSTEM_USER |
TABLE |
TEMPORARY |
THEN |
TIME |
TIMESTAMP |
TIMEZONE_HOUR |
TIMEZONE_MINUTE |
TO |
TRAILING |
TRANSACTION |
TRANSLATE |
TRANSLATION |
TRIM |
TRUE |
UNION |
UNIQUE |
UNKNOWN |
UPDATE |
UPPER |
USAGE |
USER |
USING |
VALUE |
VALUES |
VARCHAR |
VARYING |
VIEW |
WHEN |
WHENEVER |
WHERE |
WITH |
WORK |
WRITE |
YEAR |
ZONE |
|
|
|
日付に対するクエリオブクエリの使用
QueryNew 関数を使用してクエリオブジェクトを作成し、列に日付定数を挿入した場合、その日付は、クエリオブジェクトにクエリオブクエリを適用するまで、クエリオブジェクトの内部に文字列として保存されます。クエリオブジェクトにクエリオブクエリを適用すると、文字列表現が日付オブジェクトに変換されます。
クエリオブクエリでは、次のように SQL および ODBC 形式の次の日付定数をサポートしています。
- SQL 形式：次のいずれかの形式の日付、時刻、またはタイムスタンプ。
- 日付文字列：yyyy-mm-dd（例：1955-06-13）
- 時刻文字列：hh:mm:ss[.nnn]（例：14:34:30.75）
- タイムスタンプ文字列：yyyy-mm-dd hh:mm:ss[.nnn]（例」1924-01-14 12:00:00.000）
- ODBC 形式：次のいずれかの形式の日付、時刻、またはタイムスタンプ。
- 日付文字列：{d 'value'}（例：{d '2004-07-06'}）
- 時刻文字列：{t 'value'}（例：{t '13:45:30'}）
- タイムスタンプ文字列：{ts 'value'}（例：{ts '2004-07-06 13:45:30'}）
- 日付文字列：{d 'value'}（例：{d '2004-07-06'}）
注意：
日付を元の形式に変換するには、DateFormat 関数を使用して "mm/dd/yy" マスクを適用します。
<cfscript>
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]);
//writeOutput("The new query is:")
// writeDump(myQuery)
</cfscript>
<!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select amount&revenue as andColumn
from myQuery
</cfquery>
<cfdump var="#newQoQ#">
<cfscript>
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]);
//writeOutput("The new query is:")
// writeDump(myQuery)
</cfscript>
<!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select amount&revenue as andColumn
from myQuery
</cfquery>
<cfdump var="#newQoQ#">
<cfscript>
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]);
//writeOutput("The new query is:")
// writeDump(myQuery)
</cfscript>
<!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select amount&revenue as andColumn
from myQuery
</cfquery>
<cfdump var="#newQoQ#">
ビット単位の包含的論理和（|）
2 つのオペランドのうち少なくとも 1 つの対応するビットが 1 の場合に、ビット単位の包含的論理和の出力が 1 になります。次に例を示します。
<cfscript>
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]);
//writeOutput("The new query is:")
// writeDump(myQuery)
</cfscript>
<!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select amount|revenue as orColumn
from myQuery
</cfquery>
<cfdump var="#newQoQ#">
<cfscript>
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]);
//writeOutput("The new query is:")
// writeDump(myQuery)
</cfscript>
<!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select amount|revenue as orColumn
from myQuery
</cfquery>
<cfdump var="#newQoQ#">
<cfscript>
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]);
//writeOutput("The new query is:")
// writeDump(myQuery)
</cfscript>
<!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select amount|revenue as orColumn
from myQuery
</cfquery>
<cfdump var="#newQoQ#">
ビット単位の排他的論理和（^）
2 つのオペランドの対応するビットが反対の場合に、ビット単位の排他的論理和の結果が 1 になります。
次に例を示します。
cfscript>
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]);
//writeOutput("The new query is:")
// writeDump(myQuery)
</cfscript>
<!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select amount^revenue as xorColumn
from myQuery
</cfquery>
<cfdump var="#newQoQ#">
cfscript>
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]);
//writeOutput("The new query is:")
// writeDump(myQuery)
</cfscript>
<!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select amount^revenue as xorColumn
from myQuery
</cfquery>
<cfdump var="#newQoQ#">
cfscript>
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]);
//writeOutput("The new query is:")
// writeDump(myQuery)
</cfscript>
<!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select amount^revenue as xorColumn
from myQuery
</cfquery>
<cfdump var="#newQoQ#">
ビット単位の補数（~）
ビット単位の補数演算子は単項演算子です（オペランドが 1 つの場合にのみ機能します）。つまり、1 を 0 に、0 を 1 に変更します。
次に例を示します。
<cfscript>
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]);
//writeOutput("The new query is:")
// writeDump(myQuery)
</cfscript>
<!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select ~revenue as compColumn
from myQuery
</cfquery>
<cfdump var="#newQoQ#">
<cfscript>
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]);
//writeOutput("The new query is:")
// writeDump(myQuery)
</cfscript>
<!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select ~revenue as compColumn
from myQuery
</cfquery>
<cfdump var="#newQoQ#">
<cfscript>
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]);
//writeOutput("The new query is:")
// writeDump(myQuery)
</cfscript>
<!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select ~revenue as compColumn
from myQuery
</cfquery>
<cfdump var="#newQoQ#">
左シフト（<<）
この演算子は最初のオペランドのビットを左にずらします。2 番目のオペランドが、ずらす桁数を決定します。
次に例を示します。
<cfscript>
// 左シフト
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]); //writeOutput("The new query is:") // writeDump(myQuery)
</cfscript> <!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select revenue<<1 as slColumn from myQuery
</cfquery>
<cfdump var="#newQoQ#">
<cfscript>
// 左シフト
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]); //writeOutput("The new query is:") // writeDump(myQuery)
</cfscript> <!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select revenue<<1 as slColumn from myQuery
</cfquery>
<cfdump var="#newQoQ#">
<cfscript>
// 左シフト
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73}
]); //writeOutput("The new query is:") // writeDump(myQuery)
</cfscript> <!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select revenue<<1 as slColumn from myQuery
</cfquery>
<cfdump var="#newQoQ#">
右シフト（>>）
右シフト演算子 >> は数値のビットを右にずらします。左側の空いたビットには 0 が入ります。数値を 2 のべき乗で除算した場合と同様の効果です。
次に例を示します。
<cfscript>
// 右シフト
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73} ]); //writeOutput("The new query is:") // writeDump(myQuery)
</cfscript> <!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select revenue>>1 as srColumn from myQuery
</cfquery>
<cfdump var="#newQoQ#">
<cfscript>
// 右シフト
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73} ]); //writeOutput("The new query is:") // writeDump(myQuery)
</cfscript> <!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select revenue>>1 as srColumn from myQuery
</cfquery>
<cfdump var="#newQoQ#">
<cfscript>
// 右シフト
myQuery = queryNew("id,name,amount,revenue","Integer,Varchar,Integer,Integer",
[
{id=1,name="One",amount=15,revenue=35},
{id=2,name="Two",amount=18,revenue=41},
{id=3,name="Three",amount=32,revenue=73} ]); //writeOutput("The new query is:") // writeDump(myQuery)
</cfscript> <!--- クエリオブクエリ --->
<cfquery name="newQoQ" dbtype="query">
select revenue>>1 as srColumn from myQuery
</cfquery>
<cfdump var="#newQoQ#">
<!--- 初期クエリ --->
<cfquery name="initialQuery" datasource="art">
SELECT artid, artname, description, price
FROM art
WHERE price > 50000
</cfquery>
<!--- <cfdump var="#initialQuery#" > --->
<cfquery name="queryOfQueries" dbtype="query">
SELECT artid,description, price%9 as newPrice
FROM initialQuery
</cfquery>
<cfdump var="#queryOfQueries#" >
<!--- 初期クエリ --->
<cfquery name="initialQuery" datasource="art">
SELECT artid, artname, description, price
FROM art
WHERE price > 50000
</cfquery>
<!--- <cfdump var="#initialQuery#" > --->
<cfquery name="queryOfQueries" dbtype="query">
SELECT artid,description, price%9 as newPrice
FROM initialQuery
</cfquery>
<cfdump var="#queryOfQueries#" >
<!--- 初期クエリ --->
<cfquery name="initialQuery" datasource="art">
SELECT artid, artname, description, price
FROM art
WHERE price > 50000
</cfquery>
<!--- <cfdump var="#initialQuery#" > --->
<cfquery name="queryOfQueries" dbtype="query">
SELECT artid,description, price%9 as newPrice
FROM initialQuery
</cfquery>
<cfdump var="#queryOfQueries#" >
クエリオブクエリのパフォーマンスについて
クエリオブクエリのパフォーマンスは、データベースから直接アクセスされた単一テーブルのクエリオブジェクトでは高くなります。これは、データベースからアクセスされたクエリオブジェクトのメタ情報が ColdFusion で保存されるからです。
SQL 結合が発生するクエリの場合、クエリオブクエリのパフォーマンスは次のようになります。
-
2 つの列参照または定数の間に 1 つの等号のみを使用した単純な結合では、クエリオブクエリは効率的です。次に例を示します。
SELECT T1.a, b, c, d FROM T1, T2 WHERE T1.a = T2.aSELECT T1.a, b, c, d FROM T1, T2 WHERE T1.a = T2.aSELECT T1.a, b, c, d FROM T1, T2 WHERE T1.a = T2.a
-
述部に複数の式が含まれる結合では、クエリオブクエリの効率は低下します。次に例を示します。
述部に複数の式が含まれる結合では、クエリオブクエリの効率は低下します。次に例を示します。述部に複数の式が含まれる結合では、クエリオブクエリの効率は低下します。次に例を示します。述部に複数の式が含まれる結合では、クエリオブクエリの効率は低下します。次に例を示します。
クエリオブクエリの処理について
クエリオブクエリでは、列の比較を処理できます。
データ型が異なる列の比較
ColdFusion MX 7 以降には、データ型の異なる列を比較できる拡張機能が含まれています。
オペランドの一方が既知の型を持つ列である場合（列の型が不明になるのは定数のみです）、クエリオブクエリは、型が不明な定数に対して、メタデータを持つオペランドの型を適用しようとします。適用可能な型の組み合わせは次のとおりです。
ColdFusion MX 7 以降には、データ型の異なる列を比較できる拡張機能が含まれています。
オペランドの一方が既知の型を持つ列である場合（列の型が不明になるのは定数のみです）、クエリオブクエリは、型が不明な定数に対して、メタデータを持つオペランドの型を適用しようとします。適用可能な型の組み合わせは次のとおりです。
- バイナリ、文字列
- 日付、文字列
- 数値、 BigDecimal
- ブーリアン、数値
つまり、バイナリと文字列の間で適用することはできますが、日付と文字列の間では適用できません。
両方のオペランドが既知のデータ型である場合、その型は同じであることが必要です。唯一の例外として、integer、float、double の間では適用できます。
両方のオペランドが定数の場合は、まず制約の厳しい型を、次に制約の緩やかな型を値に適用しようとします。
- まずバイナリを試し、次に文字列を試す
- まず日付を試し、次に文字列を試す
- まずブール値を試し、次に数値を試す
