利用樹形查詢實現部分分析函式功能
上一篇文章中描述了PRIOR可以用在SELECT當中，這一篇描述利用這個功能使用樹形查詢模擬一些分析函式的功能。
在查詢列表中使用PRIOR操作：http://yangtingkun.itpub.net/post/468/490554
建立一個測試表：
SQL> create table t as
2 select rownum id, tname name, tabtype type
3 from tab;
Table created.
SQL> select * from t;
ID NAME TYPE
---------- ------------------------------ -------
1 REG TABLE
2 TT TABLE
3 CP_TEST_T TABLE
4 T3 TABLE
5 CELL TABLE
6 ORD_ORDER_MV TABLE
7 COMPANY TABLE
8 MESMS TABLE
9 N1 TABLE
10 N2 TABLE
11 C_SORT CLUSTER
12 DMN_DATE TABLE
13 C_NO_SORT CLUSTER
14 REC TABLE
15 HFFX TABLE
16 SYS_TEMP_FBT TABLE
17 FARM TABLE
18 DEPT TABLE
19 C_HASH_SORT CLUSTER
20 T1 VIEW
21 MY_USER VIEW
22 V_RANDOM VIEW
23 T TABLE
23 rows selected.
由於PRIOR操作的功能是獲取樹形查詢的父節點的記錄，因此只要將查詢的記錄以樹的形式串起來就能模仿LAG函式的功能。
看一個最簡單的例子：
SQL> select id, name, lag(name) over(order by id) l_name
2 from t;
ID NAME L_NAME
---------- ------------------------------ ------------------------------
1 REG
2 TT REG
3 CP_TEST_T TT
4 T3 CP_TEST_T
5 CELL T3
6 ORD_ORDER_MV CELL
7 COMPANY ORD_ORDER_MV
8 MESMS COMPANY
9 N1 MESMS
10 N2 N1
11 C_SORT N2
12 DMN_DATE C_SORT
13 C_NO_SORT DMN_DATE
14 REC C_NO_SORT
15 HFFX REC
16 SYS_TEMP_FBT HFFX
17 FARM SYS_TEMP_FBT
18 DEPT FARM
19 C_HASH_SORT DEPT
20 T1 C_HASH_SORT
21 MY_USER T1
22 V_RANDOM MY_USER
23 T V_RANDOM
23 rows selected.
SQL> select id, name, prior name l_name
2 from t
3 start with id = 1
4 connect by prior id + 1 = id;
ID NAME L_NAME
---------- ------------------------------ ------------------------------
1 REG
2 TT REG
3 CP_TEST_T TT
4 T3 CP_TEST_T
5 CELL T3
6 ORD_ORDER_MV CELL
7 COMPANY ORD_ORDER_MV
8 MESMS COMPANY
9 N1 MESMS
10 N2 N1
11 C_SORT N2
12 DMN_DATE C_SORT
13 C_NO_SORT DMN_DATE
14 REC C_NO_SORT
15 HFFX REC
16 SYS_TEMP_FBT HFFX
17 FARM SYS_TEMP_FBT
18 DEPT FARM
19 C_HASH_SORT DEPT
20 T1 C_HASH_SORT
21 MY_USER T1
22 V_RANDOM MY_USER
23 T V_RANDOM
23 rows selected.
這個功能太簡單了，下面看一個複雜一點的：
SQL> select id, name, lag(name) over(partition by type order by id) lname
2 from t;
ID NAME LNAME
---------- ------------------------------ ------------------------------
11 C_SORT
13 C_NO_SORT C_SORT
19 C_HASH_SORT C_NO_SORT
1 REG
2 TT REG
3 CP_TEST_T TT
4 T3 CP_TEST_T
5 CELL T3
6 ORD_ORDER_MV CELL
7 COMPANY ORD_ORDER_MV
8 MESMS COMPANY
9 N1 MESMS
10 N2 N1
12 DMN_DATE N2
14 REC DMN_DATE
15 HFFX REC
16 SYS_TEMP_FBT HFFX
17 FARM SYS_TEMP_FBT
18 DEPT FARM
23 T DEPT
20 T1
21 MY_USER T1
22 V_RANDOM MY_USER
23 rows selected.
SQL> select id, name, prior name lname
2 from
3 (
4 select row_number() over(partition by type order by id) rn, id, name, type
5 from t
6 )
7 start with id in (select min(id) from t group by type)
8 connect by prior rn + 1 = rn
9 and prior type = type;
ID NAME LNAME
---------- ------------------------------ ------------------------------
11 C_SORT
13 C_NO_SORT C_SORT
19 C_HASH_SORT C_NO_SORT
1 REG
2 TT REG
3 CP_TEST_T TT
4 T3 CP_TEST_T
5 CELL T3
6 ORD_ORDER_MV CELL
7 COMPANY ORD_ORDER_MV
8 MESMS COMPANY
9 N1 MESMS
10 N2 N1
12 DMN_DATE N2
14 REC DMN_DATE
15 HFFX REC
16 SYS_TEMP_FBT HFFX
17 FARM SYS_TEMP_FBT
18 DEPT FARM
23 T DEPT
20 T1
21 MY_USER T1
22 V_RANDOM MY_USER
23 rows selected.
最後配合10g的CONNECT_BY_ROOT，實現一個FIRST_VALUE函式的功能：
SQL> select id, name, first_value(name) over(partition by type order by id) fname
2 from t;
ID NAME FNAME
---------- ------------------------------ ------------------------------
11 C_SORT C_SORT
13 C_NO_SORT C_SORT
19 C_HASH_SORT C_SORT
1 REG REG
2 TT REG
3 CP_TEST_T REG
4 T3 REG
5 CELL REG
6 ORD_ORDER_MV REG
7 COMPANY REG
8 MESMS REG
9 N1 REG
10 N2 REG
12 DMN_DATE REG
14 REC REG
15 HFFX REG
16 SYS_TEMP_FBT REG
17 FARM REG
18 DEPT REG
23 T REG
20 T1 T1
21 MY_USER T1
22 V_RANDOM T1
23 rows selected.
SQL> select id, name, connect_by_root(name) f_name
2 from
3 (
4 select row_number() over(partition by type order by id) rn, id, name, type
5 from t
6 )
7 start with id in (select min(id) from t group by type)
8 connect by prior rn + 1 = rn
9 and prior type = type;
ID NAME F_NAME
---------- ------------------------------ ------------------------------
11 C_SORT C_SORT
13 C_NO_SORT C_SORT
19 C_HASH_SORT C_SORT
1 REG REG
2 TT REG
3 CP_TEST_T REG
4 T3 REG
5 CELL REG
6 ORD_ORDER_MV REG
7 COMPANY REG
8 MESMS REG
9 N1 REG
10 N2 REG
12 DMN_DATE REG
14 REC REG
15 HFFX REG
16 SYS_TEMP_FBT REG
17 FARM REG
18 DEPT REG
23 T REG
20 T1 T1
21 MY_USER T1
22 V_RANDOM T1
23 rows selected.
這篇文章是受到PRIOR操作可以用在SELECT列表中這個事實的啟發，將思路擴充套件的結果。這裡只是說使用樹形查詢可以實現什麼功能，而並不是推薦的寫法。事實上，這種方式的效率肯定要低於分析函式，且寫法要複雜的多。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-613108/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 樹形查詢
- 利用指標實現strncmp函式功能指標函式
- oracle樹形查詢Oracle
- oracle的sql查詢分析函式-高階部分-分析函授over()子句OracleSQL函式
- c++字串查詢函式實現C++字串函式
- 二分查詢（函式實現）函式
- oracle樹形選單查詢Oracle
- 迭代查詢 樹形結構及常用的函式函式
- Asp.net利用Treeview實現樹形列表ASP.NETView
- [PY3]——函式——函式註解 | 實現型別檢查功能函式型別
- 遞迴函式-樹形列表遞迴函式
- MySQL函式查詢目錄樹問題記錄MySql函式
- python之 利用字典與函式實現switch case功能Python函式
- 遞迴樹形查詢所有分類遞迴
- select查詢之五：分析函式在查詢的運用函式
- Mysql 實現樹狀遞迴查詢MySql遞迴
- Connect by實現樹查詢的妙用
- 查詢賬單功能的實現
- 利用CGI方式實現Web查詢 (轉)Web
- MySQL分析函式實現MySql函式
- Spring Boot 2 實戰：利用Redis的Geo功能實現查詢附近的位置Spring BootRedis
- 樹形結構的儲存與查詢
- Oracle 樹形結構查詢的特殊用法Oracle
- 樹形查詢也瘋狂&優化措施優化
- Java實現遞迴查詢樹結構Java遞迴
- 二叉查詢樹概念及實現
- C#實現二叉查詢樹C#
- Excel VBA小程式 -使 用VBA實現VLOOKUP函式查詢？Excel函式
- WebView實現頁內文字查詢功能WebView
- Solr複雜查詢一：函式查詢Solr函式
- 利用 DynamicLinq 實現簡單的動態表示式構建查詢
- HTML + CSS + JS 利用郵編查詢 API 實現郵編查詢工具HTMLCSSJSAPI
- SQL 部分函式的使用，子查詢，group by，虛擬欄位，case……SQL函式
- 不使用分析函式的累計統計查詢函式
- JavaScript實現簡單二叉查詢樹JavaScript
- 二叉查詢樹的實現——C++C++
- 10g樹形查詢新特性CONNECT_BY_ROOT的9i實現方式
- 10g樹形查詢新特性CONNECT_BY_ISLEAF的9i實現方式