以不同方式实现层次查询的行列转换_层次查询学习3

一、建表及插入测试数据

--建测试表及插入测试数据
-- Create table
create table TAB_CONNECT_BY
(
  CHILD  NUMBER,
  PARENT NUMBER
);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (2, 5);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (3, 5);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (10, 15);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (5, 15);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (9, 17);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (8, 17);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (15, 38);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (17, 38);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (6, 38);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (13, 26);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (1, 26);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (12, 26);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (11, 18);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (7, 18);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (38, 0);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (26, 0);
insert into TAB_CONNECT_BY (CHILD, PARENT)values (18, 0);
COMMIT;

二、几种不同的查询实现方式

--1、使用系统函数WMSYS.WM_CONCAT()
SELECT PARENT, WMSYS.WM_CONCAT(CHILD) CHILD_CHAR_LIST FROM TAB_CONNECT_BY GROUP BY PARENT;
--2、使用CONNECT BY...START WITH和LEAD()OVER()
WITH TAB_CONNECT_BY_LEAD AS
 (SELECT PARENT,
         CHILD,
         PARENT * 1000 || CHILD P1000_C,
         LEAD(PARENT * 1000 || CHILD) OVER(PARTITION BY PARENT ORDER BY CHILD DESC) P1000_C_LEAD
    FROM TAB_CONNECT_BY)
SELECT PARENT, SUBSTR(SYS_CONNECT_BY_PATH(CHILD, ','), 2) CHILD_CHAR_LIST
  FROM TAB_CONNECT_BY_LEAD
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH P1000_C_LEAD IS NULL
CONNECT BY PRIOR P1000_C = P1000_C_LEAD
 ORDER BY PARENT;
--3、使用CONNECT BY...START WITH和ROW_NUMBER()OVER()
WITH TAB_CONNECT_BY_RUM AS
 (SELECT PARENT,
         CHILD,
         ROW_NUMBER() OVER(PARTITION BY PARENT ORDER BY CHILD DESC) PAR_ROW_NUM
    FROM TAB_CONNECT_BY)
SELECT PARENT, LTRIM(MAX(SYS_CONNECT_BY_PATH(CHILD, ',')), ',') CHILD_CHAR_LIST
  FROM TAB_CONNECT_BY_RUM
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH PAR_ROW_NUM = 1
CONNECT BY PRIOR PAR_ROW_NUM = PAR_ROW_NUM - 1 AND PRIOR PARENT = PARENT
 GROUP BY PARENT, CHILD
 ORDER BY PARENT;

三、数据仓库项目中在由SDM构建SQL语句过程中的一个案例

--1、SDM原始表建表语句
-- Create table
create table DW_SDM_TAB
(
  TAB_ID           NUMBER,
  GROUP_ID         NUMBER,
  COL_ID           NUMBER,
  INT_TAB_NAME     VARCHAR2(2000),
  INT_COL_NAME     VARCHAR2(2000),
  COM_TAB_NAME     VARCHAR2(2000),
  COM_COL_NAME     VARCHAR2(2000),
  COM_COL_COMMENTS VARCHAR2(2000),
  DOMAIN_CD        VARCHAR2(2000)
);
-- Add comments to the columns 
comment on column DW_SDM_TAB.TAB_ID
  is '表ID';
comment on column DW_SDM_TAB.GROUP_ID
  is '组别';
comment on column DW_SDM_TAB.COL_ID
  is '列别';
comment on column DW_SDM_TAB.INT_TAB_NAME
  is '接口层表名';
comment on column DW_SDM_TAB.INT_COL_NAME
  is '接口层列名';
comment on column DW_SDM_TAB.COM_TAB_NAME
  is '整合层表名';
comment on column DW_SDM_TAB.COM_COL_NAME
  is '整合层列名';
comment on column DW_SDM_TAB.COM_COL_COMMENTS
  is '整合层列注释';
comment on column DW_SDM_TAB.DOMAIN_CD
  is '所属主题';
--2、行列转换查询语句
WITH DW_SDM_TAB_LEAD AS
 (SELECT TAB_ID,
         INT_TAB_NAME,
         INT_COL_NAME,
         COM_TAB_NAME,
         COM_COL_NAME,
         TO_NUMBER(TAB_ID * 1000 || COL_ID) TAB_COL_PK,
         LEAD(TO_NUMBER(TAB_ID * 1000 || COL_ID)) OVER(PARTITION BY TAB_ID ORDER BY COL_ID DESC) TAB_COL_LEAD,
         DOMAIN_CD
    FROM DW_SDM_TAB)
SELECT TAB_ID, INT_TAB_NAME, SUBSTR(SYS_CONNECT_BY_PATH(INT_COL_NAME, ','), 2) INT_COL_LIST, COM_TAB_NAME, SUBSTR(SYS_CONNECT_BY_PATH(COM_COL_NAME, ','), 2) COM_COL_LIST, DOMAIN_CD
  FROM DW_SDM_TAB_LEAD
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH TAB_COL_LEAD IS NULL
CONNECT BY PRIOR TAB_COL_PK = TAB_COL_LEAD
 ORDER BY TAB_ID;