[转]Hierarchical Queries之LEVEL应用

1.回忆一下这棵“树”
A
/ \
B   C
/   /
D   E
/ \
F   G

2.再回忆一下刻画这棵树的T表数据
sec@ora10g> select * from t;

X                   Y          Z
---------- ---------- ----------
A                   1
B                   2          1
C                   3          1
D                   4          2
E                   5          3
F                   6          4
G                   7          4

7 rows selected.

3.直入主题，引入“LEVEL”伪列。直接可以得到每一条记录的层次级别。
sec@ora10g> select x, y, z, level from t start with x = 'A' connect by prior y=z;

X                   Y          Z      LEVEL
---------- ---------- ---------- ----------
A                   1                     1
B                   2          1          2
D                   4          2          3
F                   6          4          4
G                   7          4          4
C                   3          1          2
E                   5          3          3

7 rows selected.

---------- ---------- ---------- ---------- ---------------------
A                   1                     1 A
B                   2          1          2 >B
D                   4          2          3 >>D
F                   6          4          4 >>>F
G                   7          4          4 >>>G
C                   3          1          2 >C
E                   5          3          3 >>E

7 rows selected.

5.LEVEL伪列小应用其二，得到连续递增的等差序列。
sec@ora10g> select level, 2*level, 3*level from dual connect by level <= 10;

LEVEL    2*LEVEL    3*LEVEL
---------- ---------- ----------
1          2          3
2          4          6
3          6          9
4          8         12
5         10         15
6         12         18
7         14         21
8         16         24
9         18         27
10         20         30

10 rows selected.

6.LEVEL伪列小应用其三，完成测试表数据的初始化。
1）创建待初始化数据的测试表T_LEVEL
sec@ora10g> create table t_level (x number, y number);

Table created.

2）使用层次查询完成三条初始化数据的插入
sec@ora10g> insert into t_level select level, dbms_random.random from dual connect by level <= 3;

3 rows created.

sec@ora10g> commit;

Commit complete.

3）验证初始化的三条数据
sec@ora10g> select * From t_level;

X          Y
---------- ----------
1 1229751406
2 -1.937E+09
3  158774029

7.LEVEL伪列小应用其四，“乾坤大挪移”的另外一种实现方法。
sec@ora10g> colsecoolerfor a8
sec@ora10g> select substr ('secooler', rownum, 1) "secooler" from dual connect by rownum <= length('secooler');

secooler
--------
s
e
c
o
o
l
e
r

8 rows selected.

“乾坤大挪移”初级实现请参见文章《【SQL】“乾坤大挪移”》http://space.itpub.net/519536/viewspace-622309

8.小结

• 博文量
246
• 访问量
3231807