Oracle 一对多表转逗号拼接

引言

在数据库设计中，经常会遇到一对多的关系，一个表中的一条记录对应另一个表中多条记录。在某些情况下，我们需要将这些多条记录转化为一个逗号拼接的字符串，便于查询和展示。本文将详细介绍如何在 Oracle 数据库中实现一对多表的转逗号拼接。

背景

假设有两个表：订单表（orders）和商品表（products），一个订单可以对应多个商品。订单表和商品表的结构如下：

-- 订单表（orders）
CREATE TABLE orders (
  order_id NUMBER,
  order_date DATE,
  total_amount NUMBER
);

-- 商品表（products）
CREATE TABLE products (
  order_id NUMBER,
  product_name VARCHAR2(100),
  price NUMBER
);

我们的目标是将每个订单对应的商品名称拼接成一个逗号分隔的字符串，如下所示：

订单ID	订单日期	商品名称
1	2021-01-01	商品A,商品B,商品C
2	2021-01-02	商品D,商品E,商品F,商品G
3	2021-01-03	商品H,商品I,商品J,商品K,商品L,商品M

方法一：使用LISTAGG函数

Oracle 提供了一个强大的聚合函数 LISTAGG，它可以用于将多行的某个字段拼接成一个字符串，并使用指定的分隔符。我们可以利用这个函数来实现一对多表的转逗号拼接。

首先，需要关联订单表和商品表，并使用 LISTAGG 函数将商品名称拼接成字符串。查询语句如下：

SELECT
  o.order_id,
  o.order_date,
  LISTAGG(p.product_name, ',') WITHIN GROUP (ORDER BY p.product_name) AS product_names
FROM
  orders o
  JOIN products p ON o.order_id = p.order_id
GROUP BY
  o.order_id,
  o.order_date;

在这个查询语句中，我们将订单表（orders）和商品表（products）通过订单ID进行关联。然后使用 LISTAGG 函数将每个订单对应的商品名称拼接成一个字符串，使用逗号作为分隔符，并按照商品名称进行排序。

运行以上查询语句后，将会得到以下结果：

订单ID	订单日期	商品名称
1	2021-01-01	商品A,商品B,商品C
2	2021-01-02	商品D,商品E,商品F,商品G
3	2021-01-03	商品H,商品I,商品J,商品K,商品L,商品M

这样，我们就成功地将一对多的表转化为了逗号拼接的字符串。

方法二：使用XMLAGG函数

另一种实现一对多表转逗号拼接的方法是使用 XMLAGG 函数。XMLAGG 函数可以将多条记录拼接成一个 XML 文档，并提供一些操作 XML 文档的函数。

类似地，我们需要关联订单表和商品表，并使用 XMLAGG 函数将商品名称拼接成 XML 文档。查询语句如下：

SELECT
  o.order_id,
  o.order_date,
  RTRIM(
    XMLAGG(XMLELEMENT(E, p.product_name || ',')).EXTRACT('//text()') ORDER BY p.product_name
  ) AS product_names
FROM
  orders o
  JOIN products p ON o.order_id = p.order_id
GROUP BY
  o.order_id,
  o.order_date;

在这个查询语句中，我们首先使用 XMLELEMENT 函数将每个商品名称拼接成一个 XML 元素节点，并在后面加上逗号。然后通过使用 XMLAGG 函数将这些 XML 元素节点拼接成一个 XML 文档。最后，通过使用 EXTRACT 函数提取出 XML 文档中的文本，并使用 RTRIM 函数去掉最后一个逗号。

运行以上查询语句后，将会得到与前面使用 LISTAGG 函数相同的结果。

方法三：使用递归子查询

除了使用内置函数，我们还可以使用递归子查询的方法来实现一对多表的转逗号拼接。

递归子查询是一种在查询语句中逐级递归地访问数据的技术。我们可以利用递归子查询来逐个连接每个商品名称，并使用逗号分隔。查询语句如下：

WITH recursive_query (order_id, order_date, product_names, product_name, product_level) AS (
  SELECT
    o.order_id,
    o.order_date,
    CAST(p.product_name AS VARCHAR2(4000)),
    p.product_name,
    1
  FROM
    orders o
    JOIN products p ON o.order_id = p.order_id
  UNION ALL
  SELECT
    orders.order_id,
    orders.order_date,
    recursive_query.product_names || ',' || products.product_name,
    products.product_name,
    recursive_query.product_level + 1
  FROM
    recursive_query
    JOIN products ON recursive_query.order_id = products.order_id
                   AND recursive_query.product_level + 1 <= (
                     SELECT MAX(product_level) FROM recursive_query
                     WHERE order_id = recursive_query.order_id
                   )
    JOIN orders ON orders.order_id = recursive_query.order_id
)
SELECT
  order_id,
  order_date,
  product_names
FROM
  recursive_query
WHERE
  product_level = (
    SELECT MAX(product_level)
    FROM recursive_query r
    WHERE r.order_id = recursive_query.order_id
  );