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

最新推荐文章于 2024-05-16 09:27:34 发布

SeanData

最新推荐文章于 2024-05-16 09:27:34 发布

阅读量5.3k

点赞数

本文链接：https://blog.csdn.net/Seandba/article/details/72675673

版权

Oracle 开发专栏收录该内容

25 篇文章 0 订阅

订阅专栏

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