Oracle 多行数据合并成一行数据

最新推荐文章于 2024-06-14 15:21:56 发布

原创最新推荐文章于 2024-06-14 15:21:56 发布 · 536 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle 行转列 #WM_CONCAT #SYS_CONNECT_BY_PATH

Oracle 专栏收录该内容

19 篇文章

订阅专栏

本文对比了两种使用SQL实现购物清单商品汇总的方法：一种是通过连接字符串实现，另一种则是利用路径遍历与窗口函数。展示了两种方法的优劣及应用场景。

方法一

SQL> WITH SHOPPING AS
  2  (
  3      SELECT '1' U_ID, '苹果' GOODS, '2' QTY FROM DUAL UNION ALL
  4      SELECT '2' U_ID, '梨子' GOODS, '5' QTY FROM DUAL UNION ALL
  5      SELECT '1' U_ID, '西瓜' GOODS, '4' QTY FROM DUAL UNION ALL
  6      SELECT '3' U_ID, '葡萄' GOODS, '1' QTY FROM DUAL UNION ALL
  7      SELECT '3' U_ID, '香蕉' GOODS, '1' QTY FROM DUAL UNION ALL
  8      SELECT '1' U_ID, '橘子' GOODS, '3' QTY FROM DUAL
  9  )
 10  SELECT U_ID, WMSYS.WM_CONCAT(GOODS) GOODS_SUM
 11  FROM   SHOPPING
 12  GROUP BY U_ID;
U_ID GOODS_SUM
---- --------------------------------------------------------------------------------
1    苹果,橘子,西瓜
2    梨子
3    葡萄,香蕉

方法二

SQL> WITH SHOPPING AS
  2  (
  3      SELECT '1' U_ID, '苹果' GOODS, '2' QTY FROM DUAL UNION ALL
  4      SELECT '2' U_ID, '梨子' GOODS, '5' QTY FROM DUAL UNION ALL
  5      SELECT '1' U_ID, '西瓜' GOODS, '4' QTY FROM DUAL UNION ALL
  6      SELECT '3' U_ID, '葡萄' GOODS, '1' QTY FROM DUAL UNION ALL
  7      SELECT '3' U_ID, '香蕉' GOODS, '1' QTY FROM DUAL UNION ALL
  8      SELECT '1' U_ID, '橘子' GOODS, '3' QTY FROM DUAL
  9  )
 10  SELECT U_ID, SUBSTR(MAX(SYS_CONNECT_BY_PATH(GOODS, ',')), 2)  GOODS_SUM
 11  FROM (
 12      SELECT U_ID, GOODS, ROW_NUMBER() OVER(PARTITION BY U_ID ORDER BY U_ID) RN
 13      FROM   SHOPPING
 14  )
 15  GROUP BY U_ID
 16  START WITH RN = 1
 17  CONNECT BY PRIOR RN = RN - 1 AND PRIOR U_ID = U_ID
 18  ORDER BY U_ID;
U_ID GOODS_SUM
---- --------------------------------------------------------------------------------
1    葡萄,香蕉,橘子
2    梨子
3    葡萄,香蕉