第五章 SQL聚合函数 %DLIST

创建值列表的聚合函数。

大纲

%DLIST([ALL | DISTINCT [BY(col-list)]] string-expr [%FOREACH(col-list)] [%AFTERHAVING])

参数

• ALL - 可选-指定%DLIST返回string-expr的所有值的列表。如果未指定关键字，则这是默认值。
• DISTINCT - 可选- DISTINCT子句，指定%DLIST返回一个结构化的%List，其中只包含唯一的string-expr值。
  DISTINCT可以指定BY(colo -list)子句，其中colo -list可以是单个字段，也可以是用逗号分隔的字段列表。
• string-expr - 计算结果为字符串的SQL表达式。
  通常是所选表中列的名称。
• %FOREACH(col-list) - 可选-列名或以逗号分隔的列名列表。
• %AFTERHAVING - 可选-应用在HAVING子句中的条件。

描述

%DLIST聚合函数返回一个ObjectScript %List结构，其中包含指定列中的值作为列表元素。

一个简单的%DLIST(或%DLIST ALL)返回列表，该列表由所选行中string-expr的所有非null值组成。
string-expr为NULL的行不会作为元素包含在列表结构中。

%DLIST DISTINCT返回一个列表，由所选行中string-expr的所有不同(唯一)非null值组成:%DLIST(DISTINCT col1)。
NULL不作为元素包含在%List结构中。
%DLIST(DISTINCT BY(col2) col1)返回一个元素的%List，其中只包含那些col1字段值在col2值不同(唯一)的记录中。
但是请注意，不同的col2值可能包含一个单独的NULL值。

%DLIST 和 %SelectMode

可以使用%SelectMode属性来指定%DLIST返回的数据显示模式:0=Logical(默认)，1=ODBC, 2= display。

请注意，在ODBC模式下，%DLIST用逗号分隔列值列表，而$LISTTOSTRING(默认情况下)返回%List列值中用逗号分隔的元素。

%DLIST和ORDER BY

%DLIST函数将表中多行列的值组合成%List结构的值列表。
因为在计算所有聚合字段之后，查询结果集中应用了一个ORDER BY子句，所以ORDER BY不能直接影响这个列表中的值序列。
在某些情况下，%DLIST结果可能会按顺序出现，但不应依赖此顺序。
在给定聚合结果值中列出的值不能显式排序。

相关的聚合函数

• %DLIST返回一个IRIS列表的值。
• LIST返回一个逗号分隔的值列表。
• JSON_ARRAYAGG返回值的JSON数组。
• XMLAGG返回一个串接的值字符串。

示例

下面的嵌入式SQL示例返回一个主机变量，该变量包含示例的Home_State列中列出的所有值的IRIS列表。
以字母A开头的人名表:

ClassMethod DList()
{
    &sql(
        SELECT %DLIST(Home_State)
            INTO :statelist
            FROM Sample.Person
            WHERE Home_State %STARTSWITH 'A')
    w "The states (as list):",statelist,!
    w "The states (as string):",$LISTTOSTRING(statelist,"^")
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).DList()
The states (as list):ARALAZAZAZARALAZARALALAR
The states (as string):AR^AL^AZ^AZ^AZ^AR^AL^AZ^AR^AL^AL^AR

请注意，这个IRIS列表包含具有重复值的元素。

下面的嵌入式SQL示例返回一个主机变量，该变量包含示例的Home_State列中列出的所有不同(唯一)值的IRIS列表。
以字母A开头的人名表:

ClassMethod DList1()
{
    &sql(
        SELECT %DLIST(DISTINCT Home_State)
            INTO :statelist
            FROM Sample.Person
            WHERE Home_State %STARTSWITH 'A')
    w "The states (as list):",statelist,!
    w "The states (as string):",$LISTTOSTRING(statelist,"^")
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).DList1()
The states (as list):ARALAZ
The states (as string):AR^AL^AZ

下面的SQL示例为每个州创建了一个 IRIS列表，其中包含在Home_City列中找到的所有值，以及按州列出的这些城市值的计数。
每个Home_State行包含该状态的所有Home_City值的列表。
这些名单可能包括重复的城市名称:

SELECT Home_State,
       %DLIST(Home_City) AS AllCities,
       COUNT(Home_City) AS CityCount
FROM Sample.Person
GROUP BY Home_State

也许更有用的方法是在Home_City列中列出每个州的所有不同值，如下例所示:

SELECT Home_State,
       %DLIST(DISTINCT Home_City) AS CitiesList,
       COUNT(DISTINCT Home_City) AS DistinctCities,
       COUNT(Home_City) AS TotalCities
FROM Sample.Person
GROUP BY Home_State

注意，这个示例返回每个州的不同城市名称和总城市名称的整数计数。

下面的例子返回以“A”开头的Home_State值的%List结构。
它以%List元素形式返回不同的Home_State值(distinct Home_State);
与不同的Home_City值(distinct BY(Home_City) Home_State)相对应的Home_State值，这可能包括一个唯一的Home_City NULL;
和所有Home_State值:

SELECT %DLIST(DISTINCT Home_State) AS DistStates,
       %DLIST(DISTINCT BY(Home_City) Home_State) AS DistCityStates,
       %DLIST(Home_State) AS AllStates
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A'   

下面的动态SQL示例使用%SelectMode属性为%List结构FavoriteColors日期字段指定ODBC显示模式。
ODBC模式将每个列的值作为一个逗号分隔的列表返回，$LISTTOSTRING函数指定一个不同的分隔符(在这个例子中是||)来分隔不同列的值:

ClassMethod DList2()
{
    s myquery = "SELECT %DLIST(FavoriteColors) AS colors FROM Sample.Person WHERE Name %STARTSWITH 'A'"
    s tStatement = ##class(%SQL.Statement).%New()
    s tStatement.%SelectMode=1
    s qStatus = tStatement.%Prepare(myquery)
    if qStatus'=1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    while rset.%Next() {
        w $LISTTOSTRING(rset.colors,"||"),!
    }
    w !,"End of data"
}

DHC-APP>d ##class(PHA.TEST.SQLCommand).DList2()
Orange,White||Red,White||Red||Blue||Green||Blue

End of data

下面的示例使用了%AFTERHAVING关键字。
它为每个Home_State返回一行，其中至少包含一个满足HAVING子句条件的Name值(以“M”开头的名称)。
第一个%DLIST函数返回该状态的所有名称的列表。
第二个%DLIST函数返回的列表只包含满足HAVING子句条件的名称:

SELECT Home_State,
       %DLIST(Name) AS AllNames,
       %DLIST(Name %AFTERHAVING) AS HaveClauseNames
    FROM Sample.Person
    GROUP BY Home_State
    HAVING Name LIKE 'M%'
    ORDER BY Home_state