oracle 树形表数据转换

Question

测试数据
create table TAB
(ID                         NUMBER(7),
 OBJID                      NUMBER(7),
 NAME                       VARCHAR2(25),
 PARENTID                   NUMBER(7),
 PARENTTYPEID               NUMBER(7),
 REQUESTID                  NUMBER(7),
 CREATERRELPARENTIDS        NUMBER(7),
 CONSTRAINT TAB_id_pk PRIMARY KEY (ID)
);
INSERT INTO TAB VALUES(1100,1843,'地区',1828,1828,0,1828);
INSERT INTO TAB VALUES(1201,1844,'区域',1828,1828,0,1828);
INSERT INTO TAB VALUES(1201,1846,'店铺状态',1845,1828,0,1845);
INSERT INTO TAB VALUES(1204,1849,'登记',1846,1828,0,1846);
INSERT INTO TAB VALUES(1205,1850,'正常',1846,1828,0,1846);
INSERT INTO TAB VALUES(1206,1851,'作废',1846,1828,0,1846);
INSERT INTO TAB VALUES(1207,1852,'终止',1846,1828,0,1846);
INSERT INTO TAB VALUES(1500,1880,'凯德商用',1844,1828,0,1844);
INSERT INTO TAB VALUES(1501,1881,'嘉茂',1880,1828,0,1880);
INSERT INTO TAB VALUES(1502,1882,'望京店',1881,1828,0,1881);
INSERT INTO TAB VALUES(1503,1883,'一层',1882,1828,0,1882);
INSERT INTO TAB VALUES(1504,1884,'一层北区',1883,1828,1,1883);
INSERT INTO TAB VALUES(1505,1885,'一层中区',1883,1828,1,1883);
INSERT INTO TAB VALUES(1503,1886,'二层',1882,1828,0,1882);
INSERT INTO TAB VALUES(1504,1887,'二层北区',1886,1828,1,1883);
INSERT INTO TAB VALUES(1505,1888,'二层中区',1886,1828,1,1883);
INSERT INTO TAB VALUES(1506,1889,'望城店',1881,1828,0,1881);
INSERT INTO TAB VALUES(1507,1890,'一层',1889,1828,0,1882);
INSERT INTO TAB VALUES(1508,1891,'一层北区',1890,1828,1,1883);
INSERT INTO TAB VALUES(1509,1892,'一层中区',1890,1828,1,1883);
INSERT INTO TAB VALUES(1510,1893,'二层',1889,1828,0,1882);
INSERT INTO TAB VALUES(1511,1894,'二层北区',1893,1828,1,1883);
INSERT INTO TAB VALUES(1512,1895,'二层中区',1893,1828,1,1883);
INSERT INTO TAB VALUES(1506,1896,'凯兴商用',1848,1828,0,1844);
INSERT INTO TAB VALUES(1507,1897,'嘉兴',1896,1828,0,1880);
INSERT INTO TAB VALUES(1508,1898,'亮马店',1897,1828,0,1881);
INSERT INTO TAB VALUES(1509,1899,'一层',1898,1828,0,1882);
INSERT INTO TAB VALUES(1510,1990,'一层南区',1899,1828,1,1883);
INSERT INTO TAB VALUES(1511,1991,'一层北区',1899,1828,1,1883);数据如下
ID   OBJID  NAME               PARENTID     PARENTTYPEID   REQUESTID   CREATERRELPARENTIDS
1100 1843  地区                 1828        1828           0           1828
1101 1844   区域                 1828        1828           0           1828
1201 1846  店铺状态             1845        1828          0          1845
1204 1849  登记                 1846         1828            0          1846
1205 1850  正常                 1846        1828           0           1846
1206 1851  作废                1846        1828           0           1846
1207 1852  终止                1846         1828           0           1846
1500 1880   凯德商用             1844        1828           0          1844
1501 1881  嘉茂                 1880         1828            0          1880
1502 1882  望京店               1881        1828          0          1881
1503 1883  一层                 1882        1828            0           1882
1504 1884  一层北区            1883         1828          1          1883
1505 1885  一层中区            1883        1828          1           1883
1503 1886  二层                 1882        1828            0           1882
1504 1887  二层北区             1886         1828          1          1883
1505 1888  二层中区             1886        1828          1           1883
1506 1889  望城店               1881        1828          0          1881
1507 1890  一层                 1889        1828            0           1882
1508 1891  一层北区             1890         1828          1          1883
1509 1892  一层中区             1890        1828          1           1883
1510 1893  二层                 1889        1828            0           1882
1511 1894  二层北区             1893         1828          1          1883
1512 1895  二层中区            1893        1828          1           1883
1506 1896  凯兴商用             1848        1828           0          1844
1507 1897   嘉兴                 1896         1828            0          1880
1508 1898   亮马店               1897        1828          0          1881
1509 1899   一层                 1898        1828            0           1882
1510 1990   一层南区             1899         1828          1          1883
1511 1991   一层北区             1899        1828          1           1883
如何输出为
BH          MC              REQUESTID
01    地区                0
02          区域                0
03    店铺状态            0
0301        登记                0
0302        正常                0
0303        作废                0
0304        终止                0
04        凯德商用            0
0401     嘉茂                0
040101      望京店              0
04010101    一层                0
0401010101  一层北区            1
0401010102  一层中区            1
04010102    一层                0
0401010201  一层北区            1
0401010202  一层中区            1
040102      望城店              0
04010201    一层                0
0401020101  一层北区            1
0401020102  一层中区            1
04010202    一层                0
0401020201  一层北区            1
0401020202  一层中区            1
05          凯兴商用            0
0501        嘉兴                0
050101      亮马店              0
05010101    一层                0
0501010101  一层南区            1
0501010102  一层北区            1

Answer 1

把TAB表的数据按照查询结果插入到TAB_BAK表？

Answer 2

create table TAB
(ID NUMBER(7),
 OBJID NUMBER(7),
 NAME VARCHAR2(25),
 PARENTID NUMBER(7),
 PARENTTYPEID NUMBER(7),
 REQUESTID NUMBER(7),
 CREATERRELPARENTIDS NUMBER(7),
 CONSTRAINT TAB_id_pk PRIMARY KEY (ID)
);
INSERT INTO TAB VALUES(1100,1843,'地区',1828,1828,0,1828);
INSERT INTO TAB VALUES(1201,1844,'区域',1828,1828,0,1828);
INSERT INTO TAB VALUES(1202,1846,'店铺状态',1845,1828,0,1845);
INSERT INTO TAB VALUES(1204,1849,'登记',1846,1828,0,1846);
INSERT INTO TAB VALUES(1205,1850,'正常',1846,1828,0,1846);
INSERT INTO TAB VALUES(1206,1851,'作废',1846,1828,0,1846);
INSERT INTO TAB VALUES(1207,1852,'终止',1846,1828,0,1846);
INSERT INTO TAB VALUES(1500,1880,'凯德商用',1844,1828,0,1844);
INSERT INTO TAB VALUES(1501,1881,'嘉茂',1880,1828,0,1880);
INSERT INTO TAB VALUES(1502,1882,'望京店',1881,1828,0,1881);
INSERT INTO TAB VALUES(1503,1883,'一层',1882,1828,0,1882);
INSERT INTO TAB VALUES(1504,1884,'一层北区',1883,1828,1,1883);
INSERT INTO TAB VALUES(1505,1885,'一层中区',1883,1828,1,1883);
INSERT INTO TAB VALUES(1506,1886,'二层',1882,1828,0,1882);
INSERT INTO TAB VALUES(1507,1887,'二层北区',1886,1828,1,1883);
INSERT INTO TAB VALUES(1508,1888,'二层中区',1886,1828,1,1883);
INSERT INTO TAB VALUES(1509,1889,'望城店',1881,1828,0,1881);
INSERT INTO TAB VALUES(1510,1890,'一层',1889,1828,0,1882);
INSERT INTO TAB VALUES(1511,1891,'一层北区',1890,1828,1,1883);
INSERT INTO TAB VALUES(1512,1892,'一层中区',1890,1828,1,1883);
INSERT INTO TAB VALUES(1513,1893,'二层',1889,1828,0,1882);
INSERT INTO TAB VALUES(1514,1894,'二层北区',1893,1828,1,1883);
INSERT INTO TAB VALUES(1515,1895,'二层中区',1893,1828,1,1883);
INSERT INTO TAB VALUES(1516,1896,'凯兴商用',1848,1828,0,1844);
INSERT INTO TAB VALUES(1517,1897,'嘉兴',1896,1828,0,1880);
INSERT INTO TAB VALUES(1518,1898,'亮马店',1897,1828,0,1881);
INSERT INTO TAB VALUES(1519,1899,'一层',1898,1828,0,1882);
INSERT INTO TAB VALUES(1520,1990,'一层南区',1899,1828,1,1883);
INSERT INTO TAB VALUES(1521,1991,'一层北区',1899,1828,1,1883);

Answer 3

先用分析函数row_number()over(partition by PARENTID order by id asc)取序号，然后再用递归查询的sys_connect_by_path和lpad拼串就可以得到bh了

Answer 4

你需要修改下你的父子级关系。
SQL> WITH t1 AS (SELECT ID,OBJID,NAME,PARENTID,PARENTTYPEID,REQUESTID,CREATERRELPARENTIDS,row_number()over(PARTITION BY PARENTID ORDER BY ID ASC) bh  FROM tab)
  2  SELECT replace(sys_connect_by_path(lpad(bh,2,'0'),','),',',''),NAME mc,REQUESTID ,parentid,bh
  3    FROM t1 a
  4   START WITH NOT EXISTS(SELECT 1 FROM t1 b WHERE b.objid=a.PARENTID)
  5  CONNECT BY  PRIOR  a.objid=a.PARENTID
  6  ORDER SIBLINGS BY  bh;
 
REPLACE(SYS_CONNECT_BY_PATH(LP                                                   MC                        REQUESTID PARENTID         BH
-------------------------------------------------------------------------------- ------------------------- --------- -------- ----------
01                                                                               地区                              0     1828          1
02                                                                               区域                              0     1828          2
0201                                                                             凯德商用                          0     1844          1
020101                                                                           嘉茂                              0     1880          1
02010101                                                                         望京店                            0     1881          1
0201010101                                                                       一层                              0     1882          1
020101010101                                                                     一层北区                          1     1883          1
020101010102                                                                     一层中区                          1     1883          2
0201010102                                                                       二层                              0     1882          2
020101010201                                                                     二层北区                          1     1886          1
020101010202                                                                     二层中区                          1     1886          2
02010102                                                                         望城店                            0     1881          2
0201010201                                                                       一层                              0     1889          1
020101020101                                                                     一层北区                          1     1890          1
020101020102                                                                     一层中区                          1     1890          2
0201010202                                                                       二层                              0     1889          2
020101020201                                                                     二层北区                          1     1893          1
020101020202                                                                     二层中区                          1     1893          2
01                                                                               店铺状态                          0     1845          1
0101                                                                             登记                              0     1846          1
 
REPLACE(SYS_CONNECT_BY_PATH(LP                                                   MC                        REQUESTID PARENTID         BH
-------------------------------------------------------------------------------- ------------------------- --------- -------- ----------
0102                                                                             正常                              0     1846          2
0103                                                                             作废                              0     1846          3
0104                                                                             终止                              0     1846          4
01                                                                               凯兴商用                          0     1848          1
0101                                                                             嘉兴                              0     1896          1
010101                                                                           亮马店                            0     1897          1
01010101                                                                         一层                              0     1898          1
0101010101                                                                       一层南区                          1     1899          1
0101010102                                                                       一层北区                          1     1899          2
 
29 rows selected
 
SQL> 

Answer 5

with t1 as(
select objid,name,requestid,
(case when parentid not in (select objid from tb) then -1 else parentid end) parentid
from tb
),
t2 as(
select objid,name,requestid,parentid,
row_number() over (partition by parentid order by objid) rn 
from t1
)
select 
replace(sys_connect_by_path(lpad(rn,2,'0'),','),',','') bh,
name mc,requestid
from t2
start with parentid=-1
connect by prior objid=parentid;