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

背景

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

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

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

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

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

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

Template SQL：

SELECT   PICKING_KEY,TRANSLATE (LTRIM (text, '/'), '*/', '*,') RESULTFROM (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;

结果：

图1

上图的效果，就是我想得到的结果。

虽然结果很简单，但是实现的SQL嵌套5层逻辑，涉及的知识可不少，如层次化查询、窗口函数、替换函数和删除函数。

作者认为，把这条SQL摸索透彻，差不多把SQL函数好好复习了一遍，OMG。

开始一层一层开始解析。

第一层

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

结果：

图2

这层是源数据，以PICKING_KEY为准，合并所有SHIP_MARK值，这是我想要的结果，如图1

第二层

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

结果：

图3

这层很简单，也就是赋予一个顺序值。关键一点使用到窗口函数ROW_NUMBER () OVER (PARTITION BY  PICKING_KEY ORDER BY  PICKING_KEY, ship_mark)，

平常几乎不用，现在认识它，以后就是我的朋友了。

第三层

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

结果：

图4

这层最复杂，关键点再层次化查询及金字塔式合并字符串，最终的目的就是把所有数据合并起来（虽然会把其他乱七八糟数据带出来）。

CONNECT BY PRIOR PICKING_KEY = PICKING_KEY AND PRIOR x - 1 = x这句SQL的意思大概这么讲：凡是符合继承关系的，均罗列出来。

如员工属于哪个小组，小组属于哪个部门，部门属于哪事业群。具体的用法大家看书吧，我在这里也不好讲清楚。

至于为什么PICKING_KEY = PICKING_KEY AND x - 1 = x，我也不是很清楚作者为什么这么写，我想最终的目的实现继承的关系吧。

第四层

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)

结果：

图5

经过上层精心处理后，我们想要的结果已经快出来了，这层我们只是让它排序，再次用到

函数ROW_NUMBER () OVER (PARTITION BY PICKING_KEY ORDER BY PICKING_KEY,lvl DESC)

第五层

SELECT   PICKING_KEY,TRANSLATE (LTRIM (text, '/'), '*/', '*,') RESULTFROM (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;

最终结果：

图6

最后一层了，现在需要把'/'字符去掉，还有取RN=1的数据，一切的一切浮出水面。

TRANSLATE这个函数我还是第一回撞见，用Oracle都两年了，OMG。刚开始看到时，TRANSLATE这不是“翻译”的意思吗，在这里干嘛用？后来再次翻译，也可以是转换的意思 -_-。

今天是马丁·路德·金纪念日，还记得他那句：我有个梦想吗？

I have a dream, make a nice extraordinary life