利用树形查询实现部分分析函数功能
上一篇文章中描述了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博客 ” ，链接：https://blog.itpub.net/4227/viewspace-613108/ ，如需转载，请注明出处，否则将追究法律责任。
