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

来源：互联网发布：java 获取前一天日期编辑：程序博客网时间：2024/06/18 14:28

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 employees  WHERE 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 employees  GROUP BY department_id  ORDER BY department_id;Dept. Employees------ ------------------------------------------------------------    10 Whalen    20 Hartstein; Fay    30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares    40 Mavris    50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie       s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot       ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Pat       el; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers;        Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sulliv       an; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle    60 Austin; Hunold; Pataballa; Lorentz; Ernst    70 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 employees  WHERE hire_date < '01-SEP-2003'  ORDER BY "Dept", "Date", "Name"; Dept Date      Name            Emp_list----- --------- --------------- ---------------------------------------------   30 07-DEC-02 Raphaely        Raphaely; Khoo   30 18-MAY-03 Khoo            Raphaely; Khoo   40 07-JUN-02 Mavris          Mavris   50 01-MAY-03 Kaufling        Kaufling; Ladwig   50 14-JUL-03 Ladwig          Kaufling; Ladwig   70 07-JUN-02 Baer            Baer   90 13-JAN-01 De Haan         De Haan; King   90 17-JUN-03 King            De Haan; King  100 16-AUG-02 Faviet          Faviet; Greenberg  100 17-AUG-02 Greenberg       Faviet; Greenberg  110 07-JUN-02 Gietz           Gietz; Higgins  110 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

阅读全文

0 0