Oracle多行数据合并一行显示【SQL实现详细解析】

最新推荐文章于 2023-02-09 16:43:51 发布

wsyangzuda

最新推荐文章于 2023-02-09 16:43:51 发布

阅读量3.5k

点赞数

分类专栏： Oracle 文章标签： SQL 合并优化 oracle 数据

本文链接：https://blog.csdn.net/wsyangzuda/article/details/22940177

版权

在优化报表SQL时遇到单行子查询返回多行的问题，原来的方法效率低下。通过研究，发现可以使用Oracle的层次化查询、窗口函数等将多行数据合并为一行，显著提升查询速度。文章详细解析了实现这一功能的五层SQL逻辑，包括源数据合并、顺序值赋予、层次化查询和字符串合并等步骤。

摘要由CSDN通过智能技术生成

背景

前段时间，优化一张报表，在优化一条SQL查询时候，出现这样一个问题：

结果列中，有某一列因为条件关联原因，查询的结果会报：

ORA-01427: single-row subquery returns more than one row，顾名思义就是查询的结果出现多行了，与其他列保持不一致。

对于这种状况，之前开发的逻辑是这样的：先查询除了此列外的所有结果，然后再把此列的结果一一Reset到报表里。

这样无疑增加了很大的time cost，刚开始因为数据量不多，还没出现速度上的隐患，随时间推移，便暴露出来了。对此做法，以如今pro 数据增长速度坚决不可取。

后来在网上找了例子，可以把多行数据合并成一行显示，查询速度快得惊人。当时没有好好研究其原理，现在有空摸索下其实现逻辑。

Template SQL：

SELECT   
PICKING_KEY,TRANSLATE (LTRIM (text, '/'), '*/', '*,') RESULT
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY PICKING_KEY ORDER BY PICKING_KEY,lvl DESC) rn,
         PICKING_KEY, 
         text
         FROM (SELECT  PICKING_KEY, 
                      LEVEL lvl,
                      SYS_CONNECT_BY_PATH (ship_mark, '/') text
                      FROM (SELECT 
                            ship_mark AS ship_mark,
                            PICKING_KEY, 
                            ROW_NUMBER () OVER (PARTITION BY  PICKING_KEY ORDER BY  PICKING_KEY, ship_mark) x
                            FROM 
                            (  SELECT 
                               DISTINCT ship_mark AS ship_mark,
                               PICKING_KEY 
                               FROM con_pack_carton, con_pack_order
                               WHERE CON_PACK_CARTON.CON_PACK_KEY = CON_PACK_ORDER.CON_PACK_KEY
                                AND CON_PACK_ORDER.PICKING_KEY in ('0002927779')
                                AND CON_PACK_ORDER.BATCH_NO = 1 
                               ORDER BY ship_mark,PICKING_KEY)  
                       ORDER BY  ship_mark, PICKING_KEY)
                CONNECT BY PRIOR PICKING_KEY = PICKING_KEY AND PRIOR x - 1 = x)
      )
WHERE rn = 1;

结果：