SQL 列内容合并

最新推荐文章于 2025-05-28 14:13:39 发布

转载最新推荐文章于 2025-05-28 14:13:39 发布 · 1.1k 阅读

文章标签：

#COLUMN #sql

数据库工具、配置、问题等专栏收录该内容

6 篇文章

订阅专栏

CREATE TABLE TMP10
(
   JS   NUMBER (8),
   ND   NUMBER (4),
   JM   VARCHAR2 (50)
);


INSERT INTO TMP10 (JS, ND, JM)
     VALUES (10, 2006, ‘DAQING01’);


INSERT INTO TMP10 (JS, ND, JM)
     VALUES (20, 2007, ‘DAQIN03’);


INSERT INTO TMP10 (JS, ND, JM)
     VALUES (30, 2006, ‘SHENGLI01’);




INSERT INTO TMP10 (JS, ND, JM)
     VALUES (40, 2007, ‘KELAMAYI01’);




INSERT INTO TMP10 (JS, ND, JM)
     VALUES (10, 2006, 'DAQING01');


INSERT INTO TMP10 (JS, ND, JM)
     VALUES (20, 2007, 'DAQIN03');


INSERT INTO TMP10 (JS, ND, JM)
     VALUES (30, 2006, 'SHENGLI01');


INSERT INTO TMP10 (JS, ND, JM)
     VALUES (40, 2007, 'KELAMAYI01');


    SELECT ND,
           SUM (JS) SUM_JS,
           MAX (LTRIM (SYS_CONNECT_BY_PATH (JM, ','), ',')) SUM_JM
      FROM (SELECT ND,
                   JS,
                   JM,
                   ROW_NUMBER () OVER (PARTITION BY ND ORDER BY JS) RN
              FROM TMP10)
START WITH RN = 1
CONNECT BY PRIOR RN + 1 = RN AND PRIOR ND = ND
  GROUP BY ND;