第十三章 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

image.png

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

image.png

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

• 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

image.png

下面的示例返回以“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'

image.png

下面的示例为每个州创建在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

image.png

以下示例使用%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

image.png

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

Name	Make	Model	Year
Smith,Joe	Pontiac	Firebird	1971
Smith,Joe	Saturn	SW2	1997
Smith,Joe	Pontiac	Bonneville	1999
Jones,Scott	Ford	Mustang	1966
Jones,Scott	Mazda	Miata	2000

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

Name	String_Of_Makes
Smith,Joe	PontiacSaturnPontiac

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

Name	String_Of_Makes
Smith,Joe	PontiacSaturn