Published on InterSystems Developer Community (https://community.intersystems.com)

主页 > 第十三章 SQL聚合函数 XMLAGG

文章
姚 鑫 · 一月 3, 2022 阅读大约需 4 分钟

第十三章 SQL聚合函数 XMLAGG

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

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
#SQL #Caché

源 URL:https://cn.community.intersystems.com/post/%E7%AC%AC%E5%8D%81%E4%B8%89%E7%AB%A0-sql%E8%81%9A%E5%90%88%E5%87%BD%E6%95%B0-xmlagg