LISTAGG 函數

對於查詢中的每一組，LISTAGG 彙整函數依據 ORDER BY 表達式來排序該組的列，然後將這些值串連成單一字串。

語法

LISTAGG( [DISTINCT] aggregate_expression [, 'delimiter' ] ) 
[ WITHIN GROUP (ORDER BY order_list) ]   

引數

DISTINCT

此子句在串連值之前會從指定的運算式中消除重複值。忽略結尾空格。例如，字串 'a' 和 'a ' 被視為重複。LISTAGG 會使用第一個遇到的值。如需詳細資訊，請參閱 多餘空格的意義。

aggregate_expression

任何有效運算式 (例如欄名)，用於提供要彙總的值。忽略 NULL 值和空字串。

delimiter

用來區隔串連值的字串常數。預設值為 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