oracle一条语句递归查询父子关系
博客专区 > YaZi-Liu 的博客 > 博客详情
oracle一条语句递归查询父子关系
YaZi-Liu 发表于2年前
oracle一条语句递归查询父子关系
  • 发表于 2年前
  • 阅读 151
  • 收藏 6
  • 点赞 0
  • 评论 0

【腾讯云】新注册用户域名抢购1元起>>>   

1  建表:

CREATE TABLE test_tree (
  test_id   INT  NOT NULL,
  pid       INT,
  test_val  VARCHAR(10),
  PRIMARY KEY (test_id)
);
INSERT INTO test_tree VALUES(1, 0,   '.NET');
INSERT INTO test_tree VALUES(2, 1,      'C#');
INSERT INTO test_tree VALUES(3, 1,      'J#');
INSERT INTO test_tree VALUES(4, 1,      'ASP.NET');
INSERT INTO test_tree VALUES(5, 1,      'VB.NET');
INSERT INTO test_tree VALUES(6, 0,   'J2EE');
INSERT INTO test_tree VALUES(7, 6,      'EJB');
INSERT INTO test_tree VALUES(8, 6,      'Servlet');
INSERT INTO test_tree VALUES(9, 6,      'JSP');
INSERT INTO test_tree VALUES(10, 0,  'Database');
INSERT INTO test_tree VALUES(11, 10,    'DB2');
INSERT INTO test_tree VALUES(12, 10,    'MySQL');
INSERT INTO test_tree VALUES(13, 10,    'Oracle');
INSERT INTO test_tree VALUES(14, 10,    'SQL Server');
INSERT INTO test_tree VALUES(15, 13,    'PL/SQL');
INSERT INTO test_tree VALUES(16, 15,    'Function');
INSERT INTO test_tree VALUES(17, 15,    'Procedure');
INSERT INTO test_tree VALUES(18, 15,    'Package');
INSERT INTO test_tree VALUES(19, 15,    'Cursor');
INSERT INTO test_tree VALUES(20, 14,    'T-SQL');

使用 START WITH  CONNECT BY 语句实现树状查询

使用 SYS_CONNECT_BY_PATH  函数,获取节点的全路径.

COLUMN "FullPath" 

2:查询语句

SELECT
  LEVEL,
  test_id,
  test_val,
  SYS_CONNECT_BY_PATH(test_val, '\') AS "FullPath"
FROM
  test_tree
START WITH
  pid =0
CONNECT BY PRIOR test_id = pid
ORDER SIBLINGS BY test_val;

结果:

     LEVEL    TEST_ID TEST_VAL             FullPath
---------- ---------- -------------------- -----------------------------------
         1          1 .NET                 \.NET
         2          4 ASP.NET              \.NET\ASP.NET
         2          2 C#                   \.NET\C#
         2          3 J#                   \.NET\J#
         2          5 VB.NET               \.NET\VB.NET
         1         10 Database             \Database
         2         11 DB2                  \Database\DB2
         2         12 MySQL                \Database\MySQL
         2         13 Oracle               \Database\Oracle
         3         15 PL/SQL               \Database\Oracle\PL/SQL
         4         19 Cursor               \Database\Oracle\PL/SQL\Cursor
     LEVEL    TEST_ID TEST_VAL             FullPath
---------- ---------- -------------------- -----------------------------------
         4         16 Function             \Database\Oracle\PL/SQL\Function
         4         18 Package              \Database\Oracle\PL/SQL\Package
         4         17 Procedure            \Database\Oracle\PL/SQL\Procedure
         2         14 SQL Server           \Database\SQL Server
         3         20 T-SQL                \Database\SQL Server\T-SQL
         1          6 J2EE                 \J2EE
         2          7 EJB                  \J2EE\EJB
         2          9 JSP                  \J2EE\JSP
         2          8 Servlet              \J2EE\Servlet

  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 0
博文 20
码字总数 35566
×
YaZi-Liu
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: