LIST
Synopsis
LIST([ALL | DISTINCT [BY(col-list)]] string-expr [%FOREACH(col-list)] [%AFTERHAVING])
引数
ALL | オプション — LIST が string-expr のすべての値のリストを返すように指定します。キーワードが指定されていない場合は、これが既定になります。 |
DISTINCT | オプション — 一意の string-expr 値のみを含むリストを LIST が返すように指定する DISTINCT 節。DISTINCT で BY(col-list) 従属節を指定できます。col-list には 1 つのフィールド、またはコンマ区切りのフィールドのリストを指定できます。 |
string-expr | 文字列として評価する SQL 式。通常は選択されたテーブルの列の名前です。 |
%FOREACH(col-list) | オプション — 列名、またはコンマで区切られた列名のリスト。%FOREACH の詳細は、"SELECT" を参照してください。 |
%AFTERHAVING | オプション — HAVING 節にある条件を適用します。 |
概要
LIST 集約関数は、指定された列の値のコンマ区切りのリストを返します。
LIST (または LIST ALL) は、選択された行の string-expr に対する、すべての値で構成されるコンマで区切られたリストを含む文字列を返します。コンマで区切られたリストでは、string-expr が空の文字列 ('') の行をプレースホルダのコンマによって表します。string-expr が NULL の行は、コンマで区切られたリストに含まれません。string-expr の値が 1 つのみでそれが空の文字列 ('') である場合、LIST は空の文字列を返します。
LIST DISTINCT は、選択された行の string-expr に対応するすべての異なる (一意の) 値で構成されるコンマ区切りリストを含む文字列を返します (LIST(DISTINCT col1))。NULL の string-expr は、コンマで区切られたリストに含まれません。LIST(DISTINCT BY(col2) col1) は、col2 値が個別 (一意) であるレコードのこれらの col1 フィールド値のみが含まれるコンマ区切りリストを返します。ただし、個別の col2 値には、個別値として NULL が 1 つ含まれる場合があります。
コンマを含むデータ値
LIST はコンマを使用して string-expr 値を区切るため、LIST をコンマを含むデータ値に使用しないでください。代わりに %DLIST または JSON_ARRAYAGG を使用してください。
LIST と %SelectMode
%SelectMode プロパティを使用して、LIST から返されるデータの表示モードを 0 = 論理 (既定)、1 = ODBC、2 = 表示のいずれかに指定できます。
LIST では列の値がコンマで区切られ、ODBC モードでは %List 列値内の要素がコンマで区切られることに注意してください。したがって、%List 構造で LIST を使用している場合に ODBC モードを使用すると、生成される結果があいまいになります。
LIST と ORDER BY
LIST 関数は、複数行のテーブル列の値を値の 1 つのコンマ区切りリストに結合します。すべての集約フィールドが評価された後に ORDER BY 節がクエリ結果セットに適用されるため、ORDER BY がリスト内の値の順序に直接影響することはありません。特定の状況下では、LIST の結果が順番に表示されることがありますが、この順番を信頼すべきではありません。特定の集約結果値内にリストされる値を、明示的に順序付けすることはできません。
最大 LIST サイズ
許容される最大の LIST 返り値は、システムで構成されている文字列の最大長です。システムが長い文字列に構成されている場合 (既定)、最大リストは 3,641,144 文字です。
関連する集約関数
-
LIST は、コンマで区切られた値のリストを返します。
-
%DLIST は、各値の要素を含む Caché リストを返します。
-
JSON_ARRAYAGG は、JSON 配列の値を返します。
-
XMLAGG は、値を連結した文字列を返します。
例
以下の埋め込み SQL の例では、Sample.Person テーブルの Home_State 列から先頭の文字が “A” であるすべての値を抽出し、それをコンマ区切りリストとしてホスト変数に格納し、返します。
&sql(SELECT LIST(Home_State)
INTO :statelist
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A')
WRITE "The states are:",!,statelist
このリストには重複値が含まれます。
以下の埋め込み SQL の例では、Sample.Person テーブルの Home_State 列から先頭の文字が “A” であるすべての個別値 (一意の値) を抽出し、それをコンマ区切りリストとしてホスト変数に格納し、返します。
&sql(SELECT LIST(DISTINCT Home_State)
INTO :statelist
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A')
WRITE "The distinct states are:",!,statelist
以下の SQL の例では、各州の Home_City 列にあるすべての値のコンマ区切りリストを作成し、これらの都市の値の数を州ごとにカウントします。それぞれの Home_State の行には、その州のすべての Home_City 値のリストが格納されます。これらのリストでは、都市名が重複している可能性があります。
SELECT Home_State,
COUNT(Home_City) AS CityCount,
LIST(Home_City) AS ListAllCities
FROM Sample.Person
GROUP BY Home_State
以下の例に示すように、各州の Home_City 列にあるすべての個別値のコンマ区切りリストを生成したほうが役に立つことが普通です。
SELECT Home_State,
COUNT(DISTINCT Home_City) AS DistCityCount,
COUNT(Home_City) AS TotCityCount,
LIST(DISTINCT Home_City) AS DistCitiesList
FROM Sample.Person
GROUP BY Home_State
この例は、各州の個別の都市名をカウントした整数と、都市名の合計をカウントした整数の両方を返します。
以下の例は、"A" で始まる Home_State 値のリストを返します。これは、個別の Home_State 値 (DISTINCT Home_State)、個別の Home_City 値に対応する Home_State 値 (DISTINCT BY(Home_City) Home_State、Home_City に対応する一意の NULL が 1 つ含まれる場合があります)、およびすべての Home_State 値を返します。
SELECT LIST(DISTINCT Home_State) AS DistStates,
LIST(DISTINCT BY(Home_City) Home_State) AS DistCityStates,
LIST(Home_State) AS AllStates
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A'
以下のダイナミック SQL の例では、%SelectMode プロパティを使用して、DOB 日付フィールドによって返される値のリストに ODBC 表示モードを指定しています。
ZNSPACE "SAMPLES"
SET myquery = "SELECT LIST(DOB) AS DOBs FROM Sample.Person WHERE Name %STARTSWITH 'A'"
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=1
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
WRITE !,"End of data"
以下のダイナミック SQL の例では、%FOREACH キーワードを使用します。これは、Home_State の age 値のリストを含むそれぞれ個別の Home_State の行を返します。
ZNSPACE "SAMPLES"
SET myquery = 3
SET myquery(1) = "SELECT DISTINCT Home_State,"
SET myquery(2) = "LIST(Age %FOREACH(Home_State)) AgesForState "
SET myquery(3) = "FROM Sample.Person WHERE Home_State %STARTSWITH 'M'"
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=1
SET qStatus = tStatement.%Prepare(.myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
WRITE !,"End of data"
以下の例では、%AFTERHAVING キーワードを使用します。ここでは、HAVING 節の条件 (先頭の文字が “M” である名前) を満たす Name 値が 1 つ以上ある Home_State 行をすべて返します。最初の LIST 関数は、該当の州についてすべての名前のリストを返します。2 番目の LIST 関数は、HAVING 節の条件を満たす名前のみを含むリストを返します。
SELECT Home_State,
LIST(Name) AS AllNames,
LIST(Name %AFTERHAVING) AS HavingClauseNames
FROM Sample.Person
GROUP BY Home_State
HAVING Name LIKE 'M%'
ORDER BY Home_State
関連項目
-
集約関数の概要
-
%DLIST 集約関数
-
JSON_ARRAYAGG 集約関数
-
XMLAGG 集約関数
-
SELECT 文