oracle根据相同字段拼接另外不同字段数据，用逗号隔开。

最新推荐文章于 2025-02-20 00:08:38 发布

陈闷油瓶

最新推荐文章于 2025-02-20 00:08:38 发布

阅读量2.4k

点赞数

文章标签：数据库 sql oracle

本文链接：https://blog.youkuaiyun.com/qq481121/article/details/114887502

版权

本文介绍了一种使用SQL进行复杂数据聚合的方法，通过连接多个表并利用sys_connect_by_path函数来实现同一ID下所有合同号的拼接，适用于需要将多个相关记录汇总为一条记录的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

table为表名，id为相同字段，name为不同字段

select id, ltrim(sys_connect_by_path(name, ','), ',') name
from (select id,
name,
row_number() over(partition by id order by name) rn,
count(*) over(partition by id) cnt
from table) t
where rn = cnt
start with rn = 1
connect by prior id = id
and prior rn = rn - 1
order by id;

实例
在这里插入图片描述


select id, 
        STATE,
        TASK_CODE,
        TECHNOLOGU_ID,
        TECHNOLOGU_CODE,
        TECHNOLOGU_NAME,
        CENTRE_ID,
        CENTRE_CODE,
        CENTRE_NAME,
        PRIORITY,
        TASK_ORDER,
        PRODUCT_CODE,
        PRODUCT_MODEL,
        COUNT_SUM,
        Production_Num,
        productUnit,
        SCHEDULING_DATE,
        SCHEDULING_REMARKS,
        CREATE_DATE,
        EQUIPMENT_ID,
        EQUIPMENT_CODE,
        EQUIPMENT_NAME,
        COMPLETE_DATE,
				ltrim(sys_connect_by_path(CONTRACT_NUM, ','), ',') CONTRACT_NUM
from (select id,
STATE,
        TASK_CODE,
        TECHNOLOGU_ID,
        TECHNOLOGU_CODE,
        TECHNOLOGU_NAME,
        CENTRE_ID,
        CENTRE_CODE,
        CENTRE_NAME,
        PRIORITY,
        TASK_ORDER,
        PRODUCT_CODE,
        PRODUCT_MODEL,
        COUNT_SUM,
        Production_Num,
        productUnit,
        SCHEDULING_DATE,
        SCHEDULING_REMARKS,
        CREATE_DATE,
        EQUIPMENT_ID,
        EQUIPMENT_CODE,
        EQUIPMENT_NAME,
        COMPLETE_DATE,
CONTRACT_NUM,
row_number() over(partition by id order by CONTRACT_NUM) rn,
count(*) over(partition by id) cnt
from (

SELECT A.ID,
PW.CONTRACT_NUM,
        A.STATE,
        A.TASK_CODE,
        A.TECHNOLOGU_ID,
        A.TECHNOLOGU_CODE,
        A.TECHNOLOGU_NAME,
        A.CENTRE_ID,
        A.CENTRE_CODE,
        A.CENTRE_NAME,
        A.PRIORITY,
        A.TASK_ORDER,
        A.PRODUCT_CODE,
        A.PRODUCT_MODEL,
        A.COUNT_SUM,
        NVL(SUM(B.PRODUCTION_NUM), 0) AS Production_Num,
        C.EXT1 AS productUnit,
        A.SCHEDULING_DATE,
        A.SCHEDULING_REMARKS,
        A.CREATE_DATE,
        A.EQUIPMENT_ID,
        A.EQUIPMENT_CODE,
        A.EQUIPMENT_NAME,
        A.COMPLETE_DATE

        FROM PLAN_TASK A
        LEFT JOIN Plan_Procedure_Info B
        ON A.ID = B.TASK_ID
        LEFT JOIN PLAN_PROCEDURE C
        ON B.Procedure_Id = C.ID
        LEFT JOIN PLAN_WORK PW
        ON B.WORK_ID = PW.ID
        LEFT JOIN fam_station D
        ON A.CENTRE_ID = D.LINEID
        WHERE 1=1 AND A.ID ='db0e3287-4280-49ff-93c2-93c95ca236c2'
         GROUP BY A.ID,
PW.CONTRACT_NUM,
        A.STATE,
        A.TASK_CODE,
        A.TECHNOLOGU_ID,
        A.TECHNOLOGU_CODE,
        A.TECHNOLOGU_NAME,
        A.CENTRE_ID,
        A.CENTRE_CODE,
        A.CENTRE_NAME,
        A.PRIORITY,
        A.TASK_ORDER,
        A.PRODUCT_CODE,
        A.PRODUCT_MODEL,
        A.COUNT_SUM,
        C.EXT1,
        A.SCHEDULING_DATE,
        A.SCHEDULING_REMARKS,
        A.CREATE_DATE,
        A.EQUIPMENT_ID,
        A.EQUIPMENT_CODE,
        A.EQUIPMENT_NAME,
        A.COMPLETE_DATE
        ORDER BY A.COMPLETE_DATE ASC, A.PRIORITY, A.TASK_ORDER
				)) t
where rn = cnt
start with rn = 1
connect by prior id = id
and prior rn = rn - 1
order by id;