SELECT ROW_NUMBER ()
OVER (PARTITION BY ChgNumber ORDER BY ChgNumber, InsideID)
NumId,
ChgNumber,
InvoiceNumber
FROM "000".tbChgInvoiceDetail
1 1 1111
2 2
3 3 123
4 1 20100919000003
5 1 20110914000001 13123213213
6 1 20110914000002 112312312312
7 1 20110921000005
8 1 20110921000006
9 1 20110921000039
10 1 20110921000040
11 1 20110921000041
12 1 20110921000083
13 1 20110921000105 21312313
14 1 20110927000001 555
15 2 20110927000001 666
16 1 20111216000004 1985
17 1 20111219000002
18 1 20111219000003
19 1 20111219000008
20 1 20111220000001
21 1 20120221000002
22 1 20120229000004
23 1 20120229000005 124
24 1 20120229000006
25 1 20120229000008 123
26 1 20120331000002
27 1 20120411000001
28 1 20120411000002 11111
29 2 20120411000002 22222
30 1 20120417000001
31 1 20120430000006
32 1 20120430000007
33 1 20120430000008
34 1 20120616000001 1
35 1 20120811000001
36 1 20120811000002
37 1 20120811000003
38 1 20120811000004
39 1 20120811000005
40 2 20120811000005
41 1 20120815000002 123
42 2 20120815000002 124
43 1 20120817000001
44 1 20120820000004
45 1 20120821000002
46 1 20120821000005
47 1 20120821000006
SELECT ChgNumber,
LTRIM (MAX (SYS_CONNECT_BY_PATH (InvoiceNumber, ',')), ',') InvoiceNumber
FROM (SELECT ROW_NUMBER ()
OVER (PARTITION BY ChgNumber ORDER BY ChgNumber, InsideID)
NumId,
ChgNumber,
InvoiceNumber
FROM "000".tbChgInvoiceDetail)
START WITH NumId = 1
CONNECT BY Numid - 1 = PRIOR NumId AND ChgNumber = PRIOR ChgNumber
GROUP BY ChgNumber
1 123, ,1111
2 20100919000003
3 20110914000001 13123213213
4 20110914000002 112312312312
5 20110921000005
6 20110921000006
7 20110921000039
8 20110921000040
9 20110921000041
10 20110921000083
11 20110921000105 21312313
12 20110927000001 555,666
13 20111216000004 1985
14 20111219000002
15 20111219000003
16 20111219000008
17 20111220000001
18 20120221000002
19 20120229000004
20 20120229000005 124
21 20120229000006
22 20120229000008 123
23 20120331000002
24 20120411000001
25 20120411000002 11111,22222
26 20120417000001
27 20120430000006
28 20120430000007
29 20120430000008
30 20120616000001 1
31 20120811000001
32 20120811000002
33 20120811000003
34 20120811000004
35 20120811000005 ,
36 20120815000002 123,124
37 20120817000001
38 20120820000004
39 20120821000002
40 20120821000005
41 20120821000006
简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段:
org_id,parent_id那么通过表示每一条记录的parent是谁,就可以形成一个树状结构。
用上述语法的查询可以取得这棵树的所有记录。
其中:
条件1 是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。
条件2 是连接条件,其中用PRIOR表示上一条记录,比如 CONNECT BY PRIOR org_id = parent_id就是说上一条记录的org_id 是本条记录的parent_id,即本记录的父亲是上一条记录。
条件3 是过滤条件,用于对返回的所有记录进行过滤。
自从Since Oracle 9i 开始,就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 如下例所示:
在 Oracle 10g 中,还有其他更多关于层次查询的新特性 。例如,有的时候用户更关心的是每个层次分支中等级最低的内容。那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如 果是叶子就会在伪列中显示“1”,如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。下给出了一个关于这个函数使用的例子:
在Oracle 10g 中还有一个新操作——CONNECT_BY_ROOT。 它用在列名之前用于返回当前层的根节点。如下面的例子,我可以显示出层次结构表中当前行数据所对应的最高等级节点的内容。
为了更好的查询一个树状结构的表,在Oracle的PL/SQL中提供乐一个诱人的特性——CONNECT BY子句。它大大的方便了我们查找树状表:遍历一棵树、寻找某个分支……,但还是存在一些不足。在Oracle 10G,就对这个特性做了增强。下面就举例说明一下:CONNECT_BY_ROOT一张表,有多颗子树(根节点为0),现在我想知道每个节点属于哪个子树。举例:铃音目录结构下有多个大分类:中外名曲、流行经典、浪漫舞曲……,每个大类下面又有多个子类,子类下面还可以细分。那现在想要知道每个子类分属哪个大类,或者要统计每个大类下面有多少个子类。看下面的例子,DIRINDEX分别为1、2、3的就是大分类,其他编号的都是子类或孙子类:select dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlibstart with fatherindex = 0