Oracle SQL函数listagg实现多行字符串连接

本文主要是介绍Oracle SQL函数listagg实现多行字符串连接，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

listagg 函数将组内的数据通过 order by 排序后，再连接到一起，可以指定分隔符。输入多行，输出一行或多行。常用来将值连接成逗号分隔的数据。

语法：

三个使用场景：

As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.
As a group-set aggregate, the function operates on and returns an output row for each group defined by theGROUPBY clause.
As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in thequery_partition_clause.

场景1，作用在所有行上面，返回一行；

场景2，根据 group by 子句的分组情况，每组返回一行；

场景3，作为分析函数，根据 query_partition_clause 子句的分组情况，每行返回一行（同组内的每行返回值可能是一样的）。

分别对应以上场景的例子：

场景1：

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

场景2：

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
. . .

场景3：

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

总结：知道了怎么连接多行字符串，还需要知道怎么拆分多行字符串，请看这篇文章：

http://blog.csdn.net/seandba/article/details/72669074

以上内容均是参考Oracle 11g官方文档

Oracle® Database SQL Language Reference
11g Release 2 (11.2)
E26088-03

这篇关于Oracle SQL函数listagg实现多行字符串连接的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！