目录

• Oracle聚合函数LISTAGG和WM_CONCAT简介
• • LISTAGG 函数
  • • 1.语法
    • 2.示例
    • 3.去除重复值
  • WM_CONCAT 函数
  • • 1.语法
    • 2.示例
    • 3.去除重复值
  • 比较
  • • 1.性能
    • 2.排序与分隔符
    • 3.去除重复值

Oracle聚合函数LISTAGG和WM_CONCAT简介

在处理数据库中的数据聚合任务时，我们经常需要将多行数据合并成一行字符串或列表。Oracle 提供了两种函数来实现这一目标：LISTAGG 和 WM_CONCAT。这两种函数各有特点，适用于不同的场景。

LISTAGG 函数

LISTAGG 是一个聚合函数，用于将一组值连接成一个字符串。它支持指定分隔符，并且可以设置最大长度以避免结果超出限制。

1.语法

LISTAGG(column, separator) WITHIN GROUP (ORDER BY column) 

• column: 要连接的列名。
• separator: 用作连接值之间的分隔符。
• WITHIN GROUP (ORDER BY column): 可选子句，用于指定连接结果的顺序。

2.示例

假设我们有一个表 orders，其中包含以下数据：

创建表 orders

CREATE TABLE orders (   order_id NUMBER(5) NOT NULL,   product VARCHAR2(20) NOT NULL ); 

插入数据

INSERT INTO orders (order_id, product) VALUES (1, 'Apples');  INSERT INTO orders (order_id, product) VALUES (1, 'Bananas');  INSERT INTO orders (order_id, product) VALUES (2, 'Oranges');  INSERT INTO orders (order_id, product) VALUES (2, 'Apples');  INSERT INTO orders (order_id, product) VALUES (2, 'Oranges'); 

使用上面的 orders 表，我们可以使用 LISTAGG 函数来获取每个订单的所有产品：

SELECT order_id,        LISTAGG(product, ', ') WITHIN GROUP (ORDER BY product) AS products FROM orders GROUP BY order_id; 

输出:

3.去除重复值

由于 LISTAGG 不支持 DISTINCT，我们可以通过子查询或者窗口函数来达到去除重复值的目的：

SELECT order_id,        LISTAGG(product, ', ') WITHIN GROUP (ORDER BY product) AS products FROM (   SELECT order_id, product   FROM (     SELECT order_id, product,            ROW_NUMBER() OVER (PARTITION BY order_id, product ORDER BY (NULL)) AS rn     FROM orders   )   WHERE rn = 1 ) GROUP BY order_id; 

输出:

WM_CONCAT 函数

WM_CONCAT 是另一个连接函数，它将多个值连接成一个字符串。这个函数主要用于连接少量的数据，因为它没有内置的排序功能，并且可能会遇到性能问题。

1.语法

WM_CONCAT(column) 

• column: 要连接的列名。

2.示例

使用上面的 orders 表，我们可以使用 WM_CONCAT 函数来获取每个订单的所有产品：

SELECT order_id,        WM_CONCAT(product) AS products FROM orders GROUP BY order_id; 

输出:

备注：使用 WM_CONCAT 函数没有内置的排序功能，结果可能无顺序。

3.去除重复值

在 WM_CONCAT 中去除重复值的方式非常直观，可以直接使用 DISTINCT 关键字：

SELECT order_id,        WM_CONCAT(DISTINCT product) AS products FROM orders GROUP BY order_id; 

输出:

比较

1.性能

• LISTAGG: 性能较好，尤其是在处理大量数据时。
• WM_CONCAT: 在处理大量数据时性能较差。

2.排序与分隔符

• LISTAGG: 支持排序和自定义分隔符。
• WM_CONCAT: 不支持排序，使用逗号作为默认分隔符。

3.去除重复值

• LISTAGG: 需要通过子查询或窗口函数去除重复值。
• WM_CONCAT: 可以直接使用 DISTINCT 关键字去除重复值。

LISTAGG 和 WM_CONCAT 都可以用来连接多个值，但 LISTAGG 更加灵活并且性能更优。对于大多数情况来说，推荐使用 LISTAGG 函数，特别是在需要对结果进行排序或需要自定义分隔符的情况下。在仅需要去除重复值时 WM_CONCAT 可能更方便，WM_CONCAT 可以直接使用 DISTINCT 关键字，而 LISTAGG 则需要通过子查询或窗口函数的方法来实现。