Oracle 一对多表转逗号拼接|极客笔记

Oracle 一对多表转逗号拼接

在数据库中，经常会遇到一对多关系的表结构。例如，一个订单表(order)和一个商品表(product)，一个订单对应多个商品。在某些场景下，我们需要将一对多表结构的数据进行转换，将多个相关数据拼接成一个字段，并用逗号分隔。本文将介绍在 Oracle 数据库中如何实现一对多表的转逗号拼接。

步骤1：创建示例表

首先，我们需要创建一对多关系的表结构，以便进行后续的操作。在本示例中，我们将创建订单表(order)和商品表(product)。

CREATE TABLE "ORDER" (
    "ORDER_ID" NUMBER,
    "ORDER_DATE" DATE,
    "ORDER_AMOUNT" NUMBER
);

CREATE TABLE "PRODUCT" (
    "PRODUCT_ID" NUMBER,
    "PRODUCT_NAME" VARCHAR2(100),
    "ORDER_ID" NUMBER
);

INSERT INTO "ORDER" VALUES (1, TO_DATE('2022-01-01', 'yyyy-mm-dd'), 1000);
INSERT INTO "ORDER" VALUES (2, TO_DATE('2022-01-02', 'yyyy-mm-dd'), 2000);
INSERT INTO "ORDER" VALUES (3, TO_DATE('2022-01-03', 'yyyy-mm-dd'), 3000);

INSERT INTO "PRODUCT" VALUES (1, 'Product A', 1);
INSERT INTO "PRODUCT" VALUES (2, 'Product B', 1);
INSERT INTO "PRODUCT" VALUES (3, 'Product C', 1);

INSERT INTO "PRODUCT" VALUES (4, 'Product D', 2);
INSERT INTO "PRODUCT" VALUES (5, 'Product E', 2);

INSERT INTO "PRODUCT" VALUES (6, 'Product F', 3);

订单表(order)的字段包括订单编号(order_id)、订单日期(order_date)和订单金额(order_amount)。商品表(product)的字段包括商品编号(product_id)、商品名称(product_name)和订单编号(order_id)。

步骤2：使用LISTAGG函数进行转逗号拼接

Oracle数据库中提供了LISTAGG函数，用于将多行数据拼接成一个字段，并使用指定的分隔符进行分隔。我们可以利用这个函数实现一对多表的转逗号拼接。

下面是一个示例查询，将订单表(order)和商品表(product)进行一对多关联，并将商品名称(product_name)进行逗号拼接：

SELECT 
    o."ORDER_ID",
    o."ORDER_DATE",
    o."ORDER_AMOUNT",
    LISTAGG(p."PRODUCT_NAME", ',') WITHIN GROUP (ORDER BY p."PRODUCT_ID") AS "PRODUCTS"
FROM 
    "ORDER" o
LEFT JOIN 
    "PRODUCT" p ON o."ORDER_ID" = p."ORDER_ID"
GROUP BY 
    o."ORDER_ID",
    o."ORDER_DATE",
    o."ORDER_AMOUNT";

运行以上查询，得到的结果如下：

ORDER_ID | ORDER_DATE         | ORDER_AMOUNT | PRODUCTS     
---------|--------------------|--------------|-------------------
1        | 2022-01-01 00:00:00| 1000         | Product A,Product B,Product C
2        | 2022-01-02 00:00:00| 2000         | Product D,Product E
3        | 2022-01-03 00:00:00| 3000         | Product F

可以看到，通过使用LISTAGG函数，我们成功将商品名称拼接成了一个字段，并用逗号分隔。

步骤3：处理空值

在实际的数据中，有可能某个订单没有对应的商品记录。这种情况下，由于LEFT JOIN操作，会将商品名称置为NULL。如果直接使用LISTAGG函数进行拼接，会导致结果中存在不必要的逗号。为了解决这个问题，我们需要在拼接之前进行一些处理。

SELECT 
    o."ORDER_ID",
    o."ORDER_DATE",
    o."ORDER_AMOUNT",
    CASE 
        WHEN COUNT(p."PRODUCT_ID") > 0 
        THEN LISTAGG(p."PRODUCT_NAME", ',') WITHIN GROUP (ORDER BY p."PRODUCT_ID")
        ELSE ''
    END AS "PRODUCTS"
FROM 
    "ORDER" o
LEFT JOIN 
    "PRODUCT" p ON o."ORDER_ID" = p."ORDER_ID"
GROUP BY 
    o."ORDER_ID",
    o."ORDER_DATE",
    o."ORDER_AMOUNT";

在以上查询的SELECT子句中，我们使用了CASE语句判断商品数量，如果数量大于0，则进行拼接。否则，将拼接字段置为空字符串。