分类： Java
2010-10-28 13:47:32
层次查询可以建立和格式化一个树形报表；还可以更进一步修剪树形结构的节点。下面是示例：
先准备临时数据：
CREATE TABLE lts_test_05 (ID NUMBER(4)
,last_name VARCHAR2(30)
,first_name VARCHAR2(30)
,userid VARCHAR2(30)
,start_date DATE
,manager_id NUMBER(4)
,title VARCHAR2(30)
,dept_id NUMBER(4)
,salary NUMBER(8,2))；
INSERT INTO lts_test_05 VALUES(1, 'Velas', 'Carmen', 'cvelas', SYSDATE, NULL, 'President', 50, 10000.00);
INSERT INTO lts_test_05 VALUES(2, 'Ngao', 'Lad', 'lngao', SYSDATE, 1, 'VP, Operations', 41, 8000.00);
INSERT INTO lts_test_05 VALUES(3, 'Nagay', 'Mid', 'mnagay', SYSDATE, 1, 'VP, Sales', 31, 8000.00);
INSERT INTO lts_test_05 VALUES(4, 'Quik', 'Mark', 'mquik', SYSDATE, 1, 'VP, Finance', 10, 8000.00);
INSERT INTO lts_test_05 VALUES(5, 'Rope', 'Aud', 'arope', SYSDATE, 1, 'VP, Admin', 50, 8000.00);
INSERT INTO lts_test_05 VALUES(6, 'Urgu', 'Moll', 'murgo', SYSDATE, 2, 'Warehouse Manager', 41, 7000.00);
INSERT INTO lts_test_05 VALUES(7, 'Mench', 'Rober', 'rmench', SYSDATE, 2, 'Warehouse Manager', 42, 7000.00);
INSERT INTO lts_test_05 VALUES(8, 'Biri', 'Ben', 'bb', SYSDATE, 2, 'Warehouse Manager', 43, 7000.00);
INSERT INTO lts_test_05 VALUES(9, 'Cat', 'Anto', 'acat', SYSDATE, 2, 'Warehouse Manager', 44, 7000.00);
INSERT INTO lts_test_05 VALUES(10, 'Havel', 'Marta', 'mhavel', SYSDATE, 2, 'Warehouse Manager', 45, 7000.00);
INSERT INTO lts_test_05 VALUES(11, 'Magee', 'Colin', 'cmagee', SYSDATE, 3, 'Sales Pres', 31, 3000.00);
INSERT INTO lts_test_05 VALUES(12, 'Gil', 'Henry', 'hgil', SYSDATE, 3, 'Sales Pres', 32, 3000.00);
INSERT INTO lts_test_05 VALUES(13, 'Sede', 'Yes', 'ysede', SYSDATE, 3, 'Sales Pres', 33, 3000.00);
INSERT INTO lts_test_05 VALUES(14, 'Nguy', 'Mai', 'mngay', SYSDATE, 3, 'Sales Pres', 34, 3000.00);
INSERT INTO lts_test_05 VALUES(15, 'Dumas', 'Andre', 'Adumas', SYSDATE, 3, 'Sales Pres', 35, 3000.00);
INSERT INTO lts_test_05 VALUES(16, 'Mad', 'Ele', 'emad', SYSDATE, 6, 'Stock Clerk', 41, 2000.00);
INSERT INTO lts_test_05 VALUES(17, 'Smith', 'Geo', 'gsmith', SYSDATE, 6, 'Stock Clerk', 41, 2000.00);
INSERT INTO lts_test_05 VALUES(18, 'Noz', 'Aki', 'anoz', SYSDATE, 7, 'Stock Clerk', 42, 2000.00);
INSERT INTO lts_test_05 VALUES(19, 'Patel', 'Vik', 'gsmith', SYSDATE, 7, 'Stock Clerk', 42, 2000.00);
INSERT INTO lts_test_05 VALUES(20, 'Newman', 'Chad', 'cnewman', SYSDATE, 8, 'Stock Clerk', 43, 2000.00);
INSERT INTO lts_test_05 VALUES(21, 'Marke', 'Alex', 'amarke', SYSDATE, 8, 'Stock Clerk', 43, 2000.00);
进行简单的层次查询：
SQL> SELECT LEVEL, ID, manager_id, last_name, title FROM lts_test_05
2 START WITH manager_id IS NULL
3 CONNECT BY PRIOR ID = manager_id;
LEVEL ID MANAGER_ID LAST_NAME TITLE
---------- ----- ---------- ------------------------------ ------------------------------
1 1 Velas President
2 2 1 Ngao VP, Operations
3 6 2 Urgu Warehouse Manager
4 16 6 Mad Stock Clerk
4 17 6 Smith Stock Clerk
3 7 2 Mench Warehouse Manager
4 18 7 Noz Stock Clerk
4 19 7 Patel Stock Clerk
3 8 2 Biri Warehouse Manager
4 20 8 Newman Stock Clerk
4 21 8 Marke Stock Clerk
3 9 2 Cat Warehouse Manager
3 10 2 Havel Warehouse Manager
2 3 1 Nagay VP, Sales
3 11 3 Magee Sales Pres
3 12 3 Gil Sales Pres
3 13 3 Sede Sales Pres
3 14 3 Nguy Sales Pres
3 15 3 Dumas Sales Pres
2 4 1 Quik VP, Finance
LEVEL ID MANAGER_ID LAST_NAME TITLE
---------- ----- ---------- ------------------------------ ------------------------------
2 5 1 Rope VP, Admin
21 rows selected
层次查询会按照每个节点一直遍历到底部，然后继续遍历下一个节点。
格式化层次查询：
SQL> SELECT LEVEL, ID, manager_id,
2 lpad(last_name, length(last_name) + LEVEL*2 - 2, '-') AS last_name
3 ,title, PRIOR last_name
4 FROM lts_test_05
5 START WITH manager_id IS NULL
6 CONNECT BY PRIOR ID = manager_id;
LEVEL ID MANAGER_ID LAST_NAME TITLE PRIORLAST_NAME
---------- ----- ---------- ------------------ ------------------------------ ------------------
1 1 Velas President
2 2 1 --Ngao VP, Operations Velas
3 6 2 ----Urgu Warehouse Manager Ngao
4 16 6 ------Mad Stock Clerk Urgu
4 17 6 ------Smith Stock Clerk Urgu
3 7 2 ----Mench Warehouse Manager Ngao
4 18 7 ------Noz Stock Clerk Mench
4 19 7 ------Patel Stock Clerk Mench
3 8 2 ----Biri Warehouse Manager Ngao
4 20 8 ------Newman Stock Clerk Biri
4 21 8 ------Marke Stock Clerk Biri
3 9 2 ----Cat Warehouse Manager Ngao
3 10 2 ----Havel Warehouse Manager Ngao
2 3 1 --Nagay VP, Sales Velas
3 11 3 ----Magee Sales Pres Nagay
3 12 3 ----Gil Sales Pres Nagay
3 13 3 ----Sede Sales Pres Nagay
3 14 3 ----Nguy Sales Pres Nagay
3 15 3 ----Dumas Sales Pres Nagay
2 4 1 --Quik VP, Finance Velas
2 5 1 --Rope VP, Admin Velas
21 rows selected
这样能更直观的看到各节点的树形关系。
修剪节点：
1）如果在where子句只会删除节点，不影响后代；
2）如果在connect by 后面条件，将会删除该节点和后代。
其他应用：
1）查询有多少个层次，包括跟节点：
SQL> SELECT COUNT(DISTINCT LEVEL)
2 FROM lts_test_05
3 START WITH manager_id IS NULL
4 CONNECT BY PRIOR ID = manager_id;
COUNT(DISTINCTLEVEL)
--------------------
4
2）查询每个层次总有多少个节点：
SQL> SELECT LEVEL, COUNT(ID)
2 FROM lts_test_05
3 START WITH manager_id IS NULL
4 CONNECT BY PRIOR ID = manager_id
5 GROUP BY LEVEL;
LEVEL COUNT(ID)
---------- ----------
1 1
2 4
4 6
3 10
总之还可以在层次查询基础上实现多种形式的查询。
