階層式擷取 T-SQL

2015-01-13

2775
0
Oracle
2015-06-04

階層式擷取 T-SQL

作者：羅駿紘精誠資訊恆逸教育訓練中心資深講師
技術分類：資料庫

我們都知道，在公司中，每個員工都應該有主管(除了總經理)，而且主管也算是公司的員工之一，也是身為某人的下屬。但關聯式資料庫存放資料的方式卻不是以階層式的方式來存放，因此想要只利用一隻單純的SQL指令來呈現出公司組織圖的關係(如下圖)，基本上是不容易的，通常需要再搭配其他的程式語言才能做到。

然而，Oracle早就想到這部份，因此陸續提供幾個功能來解決這個問題。

方法一：透過階層式查詢(Hierarchical Query)

SELECT employee_id, LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') AS org_chart,manager_id , level
FROM employees
START WITH first_name='Steven' AND last_name='King'
CONNECT BY PRIOR employee_id=manager_id
order by level;

方法二：透過RECURSIVE WITH

WITH
reports_to_100 (eid, emp_last, mgr_id, reportLevel) AS
( --Anchor Member(不變成員)
     SELECT employee_id, last_name, manager_id, 1 reportLevel
     FROM employees
     WHERE employee_id = 100
   UNION ALL
     --Recursive Member(遞迴成員)
     SELECT e.employee_id, e.last_name, e.manager_id, reportLevel+1
     FROM reports_to_100 r, employees e
     WHERE r.eid = e.manager_id
)
SELECT eid, LPAD(emp_last, LENGTH(emp_last)+(reportLevel*2)-2,'_')
       AS org_chart, mgr_id, reportLevel
FROM reports_to_100;

以上二種方法都可以得到相同結果如下。

實作 TIPTOP BOM

SELECT LEVEL,bmb02,bmb01,bmb03,bmb04,bmb05,bmb06,bmb07
   FROM bmb_file, bma_file, ima_file
WHERE bma01 = bmb01
    AND bmaacti = 'Y'
    AND bma01 = ima01
START WITH bma01 = '7A01A6A111A230-MZZ'
    AND bmaacti = 'Y'
CONNECT BY PRIOR bmb03 = bmb01
    AND bmaacti = 'Y'

WITH
reports_to_100 (reportLevel,bmb02,bmb01,bmb03,bmb04,bmb05,bmb06,bmb07) AS
( --Anchor Member(不變成員)
SELECT 1,bmb02,bmb01,bmb03,bmb04,bmb05,bmb06,bmb07
   FROM bmb_file, bma_file, ima_file
WHERE bma01 = bmb01
    AND bmaacti = 'Y'
    AND bma01 = ima01
    AND bma01= '7A01A6A111A230-MZZ'
   UNION ALL
     --Recursive Member(遞迴成員)
SELECT reportLevel+1,b.bmb02,b.bmb01,b.bmb03,b.bmb04,b.bmb05,b.bmb06,b.bmb07
   FROM reports_to_100 r,bmb_file b
WHERE r.bmb03 = b.bmb01
)
SELECT *
       --eid, LPAD(emp_last, LENGTH(emp_last)+(reportLevel*2)-2,'_')
       --AS org_chart, mgr_id, reportLevel
FROM reports_to_100;

回首頁