LISTAGG 函数
对于查询中的每个组,LISTAGG 聚合函数根据 ORDER BY 表达式对该组的行进行排序,然后将值串联成一个字符串。
语法
LISTAGG( [DISTINCT] aggregate_expression [, 'delimiter' ] ) [ WITHIN GROUP (ORDER BY order_list) ]
参数
- DISTINCT
-
用于在串联之前消除指定表达式中重复值的子句。将忽略尾随空格。例如,字符串
'a'
和'a '
视为重复项。LISTAGG 将使用遇到的第一个值。有关更多信息,请参阅 尾部空格的意义。 - aggregate_expression
-
提供要聚合的值的任何有效表达式,如列名称。忽略 NULL 值和空字符串。
- 分隔符
-
用于分隔串联的值的字符串常数。默认值为 NULL。
- WITHIN GROUP (ORDER BY order_list)
-
用于指定聚合值的排序顺序的子句。
返回值
VARCHAR(MAX)。如果结果集大于最大 VARCHAR 大小,则 LISTAGG 返回以下错误:
Invalid operation: Result size exceeds LISTAGG limit
使用说明
如果语句包含多个使用 WITHIN GROUP 子句的 LISTAGG 函数,则每个 WITHIN GROUP 子句必须使用相同的 ORDER BY 值。
例如,以下语句将返回错误。
SELECT LISTAGG(sellerid) WITHIN GROUP (ORDER BY dateid) AS sellers, LISTAGG(dateid) WITHIN GROUP (ORDER BY sellerid) AS dates FROM sales;
以下各条语句将成功运行。
SELECT LISTAGG(sellerid) WITHIN GROUP (ORDER BY dateid) AS sellers, LISTAGG(dateid) WITHIN GROUP (ORDER BY dateid) AS dates FROM sales; SELECT LISTAGG(sellerid) WITHIN GROUP (ORDER BY dateid) AS sellers, LISTAGG(dateid) AS dates FROM sales;
示例
以下示例聚合卖家 ID(按卖家 ID 进行排序)。
SELECT LISTAGG(sellerid, ', ') WITHIN GROUP (ORDER BY sellerid) FROM sales WHERE eventid = 4337;
listagg ---------------------------------------------------------------------------------------------------------------------------------------- 380, 380, 1178, 1178, 1178, 2731, 8117, 12905, 32043, 32043, 32043, 32432, 32432, 38669, 38750, 41498, 45676, 46324, 47188, 47188, 48294
以下示例使用 DISTINCT 返回唯一卖家 ID 的列表。
SELECT LISTAGG(DISTINCT sellerid, ', ') WITHIN GROUP (ORDER BY sellerid) FROM sales WHERE eventid = 4337;
listagg ------------------------------------------------------------------------------------------- 380, 1178, 2731, 8117, 12905, 32043, 32432, 38669, 38750, 41498, 45676, 46324, 47188, 48294
以下示例按日期顺序聚合卖家 ID。
SELECT LISTAGG(sellerid, ', ') WITHIN GROUP (ORDER BY dateid) FROM sales WHERE eventid = 4337; listagg ----------------------------------------------------------------------------------------------------------------------------------------- 41498, 47188, 47188, 1178, 1178, 1178, 380, 45676, 46324, 48294, 32043, 32043, 32432, 12905, 8117, 38750, 2731, 32432, 32043, 380, 38669
以下示例返回 ID 为 660 的买家的销售日期的竖线分隔的列表。
SELECT LISTAGG( (SELECT caldate FROM date WHERE date.dateid=sales.dateid), ' | ' ) WITHIN GROUP (ORDER BY sellerid DESC, salesid ASC) FROM sales WHERE buyerid = 660;
listagg ------------------------------------------------- 2008-07-16 | 2008-07-09 | 2008-01-01 | 2008-10-26
以下示例返回买家 ID 660、661 和 662 的销售 ID 的逗号分隔列表。
SELECT buyerid, LISTAGG(salesid,', ') WITHIN GROUP (ORDER BY salesid) AS sales_id FROM sales WHERE buyerid BETWEEN 660 AND 662 GROUP BY buyerid ORDER BY buyerid;
buyerid | sales_id --------+----------------------------------------------------- 660 | 32872, 33095, 33514, 34548 661 | 19951, 20517, 21695, 21931 662 | 3318, 3823, 4215, 51980, 53202, 55908, 57832, 171603