第十三章 SQL聚合函数 XMLAGG

一个聚合函数，它创建一个串接的值字符串。

大纲

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

参数

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

描述

XMLAGG聚合函数返回由string-expr中的所有值组成的串接字符串。
返回值的数据类型为VARCHAR，默认长度为4096。

• 一个简单的XMLAGG(或XMLAGG ALL)返回一个字符串，该字符串包含一个由所选行中string-expr的所有值组成的连接字符串。
  string-expr为NULL的行将被忽略。

以下两个示例都返回相同的单个值，即Sample.Person表的Home_State列中列出的所有值的串联字符串。

SELECT XMLAGG(Home_State) AS All_State_Values
FROM Sample.Person

SELECT XMLAGG(ALL Home_State) AS ALL_State_Values
FROM Sample.Person

请注意，此连接字符串包含重复值。

• XMLAGG DISTINCT返回由所选行中string-expr的所有不同(唯一)值组成的连接字符串：XMLAGG(DISTINCT COL1)。将忽略string-expr为NULL的行。XMLAGG(DISTINCT BY(Col2)col1)返回一个串联字符串，该字符串仅包含col2值不同(唯一)的记录中的col1字段值。然而，请注意，不同的col2值可以包括单个NULL作为不同的值。

返回值中将省略string-expr为NULL的行。如果至少返回一个非空字符串值，则从返回值中省略string-expr为空字符串('')的行。如果唯一非空的string-expr值是空字符串('')，则返回值是单个空字符串。

XMLAGG不支持数据流字段。为string-expr指定流字段会导致SQLCODE-37。

XML和XMLAGG

XMLAGG的一个常见用法是标记列中的每个数据项。这是通过组合XMLAGG和XMLELEMENT来实现的，如下例所示：

SELECT XMLAGG(XMLELEMENT("para",Home_State))
FROM Sample.Person

这将产生如下所示的输出字符串：

<para>LA</para><para>MN</para><para>LA</para><para>NH</para><para>ME</para>...

XMLAGG和ORDER BY

XMLAGG函数将来自多行的表列的值连接到单个字符串中。因为在计算所有聚合字段之后，将逐个从句应用于查询结果集，所以逐个不能直接影响该字符串中的值序列。在某些情况下，XMLAGG结果可能会按顺序显示，但不应依赖此排序。给定聚合结果值中列出的值无法显式排序。

相关聚合函数

• XMLAGG返回一个串连的值字符串。
• LIST返回值的逗号分隔列表。
• %DLIST返回包含每个值的元素IRIS列表。
• JSON_ARRAYAGG返回值的JSON数组。

示例

下面的示例创建在Sample.Person表的FavoriteColors列中找到的所有不同值的串联字符串。因此，对于ALL_COLLES列，每行都有相同的值。请注意，虽然有些行的FavoriteColors为空值，但该值不包括在连接的字符串中。数据值以内部格式返回。

SELECT Name,FavoriteColors,
   XMLAGG(DISTINCT FavoriteColors) AS All_Colors_In_Table
FROM Sample.Person
ORDER BY FavoriteColors

下面的示例返回以“A”开头的Home_State值的串联字符串。它返回不同的Home_State值(不同的Home_State)；与不同的Home_City值对应的Home_State值(与(Home_City)Home_State不同)，其中可能包括Home_City的一个唯一NULL；以及所有Home_State值：

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

下面的示例为每个州创建在Home_City列中找到的所有不同值的串联字符串。同一州的每一行都包含该州所有不同城市值的列表：

SELECT Home_State, Home_City,
   XMLAGG(DISTINCT Home_City %FOREACH(Home_State)) AS All_Cities_In_State
FROM Sample.Person
ORDER BY Home_State

以下示例使用%AFTERHAVING关键字。它为每个包含至少一个满足HAVING子句条件(以“C”或“K”开头的名称)的Name值的Home_State返回一行。第一个XMLAGG函数返回由该州的所有名称组成的连接字符串。第二个XMLAGG函数返回仅由满足HAVING子句条件的名称组成的连接字符串：

SELECT Home_State,
       XMLAGG(Name) AS AllNames,
       XMLAGG(Name %AFTERHAVING) AS HaveClauseNames
    FROM Sample.Person
    GROUP BY Home_State
    HAVING Name LIKE 'C%' OR Name LIKE 'K%' 
    ORDER BY Home_state

对于以下示例，假设我们有下表AutoClub：

SELECT DISTINCT Name, XMLAGG(Make) AS String_Of_Makes
FROM AutoClub WHERE Name = 'Smith,Joe'

SELECT DISTINCT Name, XMLAGG(DISTINCT Make) AS String_Of_Makes
FROM AutoClub WHERE Name = 'Smith,Joe'