Oracle函数之listagg函数

article/2025/10/4 8:38:34

语法

在这里插入图片描述
有点难以看懂，个人理解listagg是list aggregate的缩写（错了勿喷），也就是列表总计，聚合的意思。
官方文档解释为：

LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.

大概意思就是listagg函数先在每个组里用order by子句排序之后，再拼接每个分组里特指的列值。
从上面的解释可以知道，listagg每个组进行操作并返回一个输出行。

注意：这里的组在没有group by子句的时候指的是整个结果集，partition by子句指的是结果集中分区。

参数解释

上图的参数有：

ALL：ALL关键字是可选的。
measure_expr ：measure_expr是被操作的列，可以是任何表达式。被操作的列中的空值将被忽略。
delimiter：分隔符，用来分割被操作列的列值，就是拼接每个列值后面跟一个分隔符，最后一个列值除外，分隔符也是可选的，默认值为NULL。
listagg_overflow_clause：这个子句控制当返回值超过返回数据类型的最大长度时函数的行为。也是可选的。
order_by_clause：顾名思义，排序子句。决定被连接值的顺序，谁在前谁在后。
query_partition_clause：listagg根据query_partition_clause中的一个或多个表达式将查询结果集划分为多个区(partition)。即分区之后，再进行列值拼接，注意partition的字段没有进行去重，仅仅是分区后进行处理，不会改变行数。Parttion by 关键字是Oracle中分析函数的一部分，它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的结果。

实例

聚合函数

SELECT LISTAGG(last_name, '; ')WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",MIN(hire_date) "Earliest"FROM employeesWHERE department_id = 30;Emp_list                                                     Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            07-DEC-02

SELECT department_id "Dept.",LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees"FROM employeesGROUP BY department_idORDER BY department_id;Dept. Employees
------ ------------------------------------------------------------10 Whalen20 Hartstein; Fay30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares40 Mavris50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davies; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Patel; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers;Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sullivan; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle60 Austin; Hunold; Pataballa; Lorentz; Ernst70 Baer
. . .

SELECT department_id "Dept.",LISTAGG(last_name, '; ' ON OVERFLOW TRUNCATE '...')WITHIN GROUP (ORDER BY hire_date) "Employees"FROM employeesGROUP BY department_idORDER BY department_id;Dept. Employees
------ ------------------------------------------------------------10 Whalen20 Hartstein; Fay30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares40 Mavris50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davies; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; ... (23)70 Baer
. . .

分析函数

SELECT department_id "Dept", hire_date "Date", last_name "Name",LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)OVER (PARTITION BY department_id) as "Emp_list"FROM employeesWHERE hire_date < '01-SEP-2003'ORDER BY "Dept", "Date", "Name";Dept Date      Name            Emp_list
----- --------- --------------- ---------------------------------------------30 07-DEC-02 Raphaely        Raphaely; Khoo30 18-MAY-03 Khoo            Raphaely; Khoo40 07-JUN-02 Mavris          Mavris50 01-MAY-03 Kaufling        Kaufling; Ladwig50 14-JUL-03 Ladwig          Kaufling; Ladwig70 07-JUN-02 Baer            Baer90 13-JAN-01 De Haan         De Haan; King90 17-JUN-03 King            De Haan; King100 16-AUG-02 Faviet          Faviet; Greenberg100 17-AUG-02 Greenberg       Faviet; Greenberg110 07-JUN-02 Gietz           Gietz; Higgins110 07-JUN-02 Higgins         Gietz; Higgins