查询问题

Question

求转换的sql

Answer 1

id列可以不要
CREATE TABLE SCOTT.TEST
(
  CODE       VARCHAR2(10 BYTE),
  NAME       VARCHAR2(10 BYTE),
  SUPERITEM  VARCHAR2(10 BYTE),
  LEVELNO    VARCHAR2(2 BYTE)
)

Answer 2

--第2层的name2没有解决，你再看看吧SQL> select * from test;        ID CODE         NAME               SUPERITE    LEVELNO
---------- ------------ ------------------ -------- ----------
         1 01           江苏省             0                 1
         2 0101         常州市             01                2
         3 0102         南京市             01                2
         4 010101       武进区             0101              3
         5 02           山东省             0                 1
         6 0201         济南市             02                2
         7 0202         青岛市             02                2
         8 0203         淄博市             02                2
         9 020101       历城区             0201              39 rows selected.SQL> SELECT code itemid,connect_by_root(code) code1,
  2  connect_by_root(NAME) name1,
  3  Decode(levelno,2,code,3,superitemid,NULL) code2,
  4  Decode(levelno,1,NULL,NAME) name2 ,
  5  Decode(levelno,3,code,NULL) code3,
  6  Decode(levelno,3,NAME,NULL) name3
  7  FROM test
  8  START WITH superitemid='0'
  9  CONNECT BY PRIOR code=superitemid
 10  ORDER BY  id
 11  ;ITEMID       CODE1        NAME1              CODE2        NAME2              CODE3        NAME3
------------ ------------ ------------------ ------------ ------------------ ------------ ----------
01           01           江苏省
0101         01           江苏省             0101         常州市
0102         01           江苏省             0102         南京市
010101       01           江苏省             0101         武进区             010101       武进区
02           02           山东省
0201         02           山东省             0201         济南市
0202         02           山东省             0202         青岛市
0203         02           山东省             0203         淄博市
020101       02           山东省             0201         历城区             020101       历城区9 rows selected.SQL> 

Answer 3

--OK了
SQL> 
SQL> SELECT code itemid,connect_by_root(code) code1,
  2  connect_by_root(NAME) name1,
  3  Decode(levelno,2,code,3,superitemid,NULL) code2,
  4  Decode(levelno,1,NULL,3,(SELECT NAME FROM test WHERE code=a.superitemid),name) name2 ,
  5  Decode(levelno,3,code,NULL) code3,
  6  Decode(levelno,3,NAME,NULL) name3
  7  FROM test a
  8  START WITH superitemid='0'
  9  CONNECT BY PRIOR code=superitemid
 10  ORDER BY  id
 11  ;ITEMID       CODE1        NAME1              CODE2        NAME2              CODE3        NAME3
------------ ------------ ------------------ ------------ ------------------ ------------ ----------
01           01           江苏省
0101         01           江苏省             0101         常州市
0102         01           江苏省             0102         南京市
010101       01           江苏省             0101         常州市             010101       武进区
02           02           山东省
0201         02           山东省             0201         济南市
0202         02           山东省             0202         青岛市
0203         02           山东省             0203         淄博市
020101       02           山东省             0201         济南市             020101       历城区9 rows selected.SQL>