LEVEL
Parent Child Parent Qty
Child Qty
1
A B
1 3
2
B C
2 3
3
C D
5 6
4
D E
1 2
1
A Z
1 3
A是成品
B，C，D是半成品
E，Z是原材料
从上面一个比例关系可以计算出，做一个A最终需要10.8个E和3个Z，
也就是能看到下面的结果
Parent
Child QTY
A
E 10.8
A
Z 3
PARENT
CHILD LEVEL1 RN
RN1
1
A B 1
*3 3
2
B C 2
*3*1.5 4.5
3
C D 3
*3*1.5*1.2 5.4
4
D E 4
*3*1.5*1.2*2 10.8
5
A Z 1
*3 3
得到算式,
等待高手出来继续.
select
parent, child, '1'||x as x
from
(
select a.*,
sys_connect_by_path(cqty/pqty, '*') as x
from
(
select 1 as
lv, 'A' as parent, 'B' as child, 1 as pqty, 3 as cqty from dual
union all
select 2 as
lv, 'B' as parent, 'C' as child, 2 as pqty, 3 as cqty from dual
union all
select 3 as
lv, 'C' as parent, 'D' as child, 5 as pqty, 6 as cqty from dual
union all
select 4 as
lv, 'D' as parent, 'E' as child, 1 as pqty, 2 as cqty from dual
union all
select 1 as
lv, 'A' as parent, 'Z' as child, 1 as pqty, 3 as cqty from
dual
)
a
start with
lv=1
connect by
parent=prior child
)
where child
in ('Z','E')
/
P C
X
- -
--------------------
D E
1*3*1.5*1.2*2
A Z
1*3
SQL>
select * from test_0712;
LE PARENT
CHILD PA CH
-- ------
----- -- --
1 A
B
1
3
2 B
C
2
3
3 C
D
5
6
4 D
E
1
2
1 A
Z
1
3
SQL>
SQL> select
t1.parent,t4.child,t1.bili*t2.bili*t3.bili*t4.bili qty
from
2
(select t.parent,t.child_qty/t.parent_qty bili
from test_0712 t where levell=1 )
t1,
3
(select t.child_qty/t.parent_qty bili from
test_0712 t where levell=2 ) t2,
4
(select t.child_qty/t.parent_qty bili from
test_0712 t where levell=3 ) t3,
5
(select t.child,t.child_qty/t.parent_qty bili
from test_0712 t where levell=4 ) t4
6
union
7
select
t5.parent,t5.child,t5.child_qty/t5.parent_qty bili from
test_0712 t5 where levell=1 and
Child='Z'
8
/
PARENT CHILD
QTY
------ -----
----------
A
E
10.8
A
Z
3
create or
replace function strtonumber(preadd number,str in varchar2 ) return
number
as
n1
number;
n2
number;
strleft
varchar2(100);
midadd
number;
begin
if str is
null or str='' then
return preadd ;
end if;
select
decode(substr(str,1,instr(str,'*')-1),null,0,substr(str,1,instr(str,'*')-1))
into n1 from dual;
if n1=0
then
select preadd*str into n1 from dual;
return n1;
end
if;
select
substr(str,instr(str,'*')+1,decode(sign(instr(str,'*',1,2)-1),1,instr(str,'*',1,2)-instr(str,'*')-1,length(str)-instr(str,'*')))
into n2 from dual;
midadd:=preadd*n1*n2;
if(instr(str,'*',1,2)>1) then
strleft:=substr(str,instr(str,'*',1,2)+1,length(str)-instr(str,'*',1,2));
return
strtonumber(midadd,strleft);
else
return midadd;
end if;
end;
SQL> CREATE
TABLE T_LEVEL
2
(
3
LEVELS NUMBER,
4
PARENT
VARCHAR2(10),
5
CHILD
VARCHAR2(10),
6
PARENT_QTY
NUMBER,
7
CHILD_QTY NUMBER
8
);
表已创建。
SQL> INSERT
INTO T_LEVEL VALUES (1, 'A', 'B', 1, 3);
已创建 1
行。
SQL> INSERT
INTO T_LEVEL VALUES (2, 'B', 'C', 2, 3);
已创建 1
行。
SQL> INSERT
INTO T_LEVEL VALUES (3, 'C', 'D', 5, 6);
已创建 1
行。
SQL> INSERT
INTO T_LEVEL VALUES (4, 'D', 'E', 1, 2);
已创建 1
行。
SQL> INSERT
INTO T_LEVEL VALUES (1, 'A', 'Z', 1, 3);
已创建 1
行。
SQL>
COMMIT;
提交完成。
SQL> SELECT *
FROM T_LEVEL;
LEVELS PARENT CHILD PARENT_QTY
CHILD_QTY
----------
---------- ---------- ---------- ----------
1 A B
1
3
2 B C
2
3
3 C D
5
6
4 D E
1
2
1 A Z
1
3
SQL> COL C
FORMAT A10
SQL> SELECT
P,
2
SUBSTR(MAX(D), LENGTH(MAX(D)))
C,
3
POWER(10, SUM(LOG(10, Q))) Q
4
FROM
5
(
6
SELECT CONNECT_BY_ROOT(PARENT)
P,
7
SYS_CONNECT_BY_PATH(CHILD, '/')
D,
8
CHILD_QTY/PARENT_QTY Q
9
FROM T_LEVEL
10
START WITH LEVELS = 1
11
CONNECT BY PRIOR CHILD = PARENT
12
)
13
GROUP BY P, SUBSTR(D, 1, 2);
P
C Q
----------
---------- ----------
A
Z 3
A
E 10.8
9i，可以
SQL> SELECT
SUBSTR(P, 2, 1) P,
2
SUBSTR(MAX(D), LENGTH(MAX(D)))
C,
3
POWER(10, SUM(LOG(10, Q))) Q
4
FROM
5
(
6
SELECT SYS_CONNECT_BY_PATH(PARENT, '/')
P,
7
SYS_CONNECT_BY_PATH(CHILD, '/')
D,
8
CHILD_QTY/PARENT_QTY Q
9
FROM T_LEVEL
10
START WITH LEVELS = 1
11
CONNECT BY PRIOR CHILD = PARENT
12
)
13
GROUP BY SUBSTR(P, 2, 1), SUBSTR(D, 2,
1);
P
C Q
--
---------- ----------
A
E 10.8
A
Z 39i，可以
SQL> SELECT
SUBSTR(P, 2, 1) P,
2
SUBSTR(MAX(D), LENGTH(MAX(D)))
C,
3
POWER(10, SUM(LOG(10, Q))) Q
4
FROM
5
(
6
SELECT SYS_CONNECT_BY_PATH(PARENT, '/')
P,
7
SYS_CONNECT_BY_PATH(CHILD, '/')
D,
8
CHILD_QTY/PARENT_QTY Q
9
FROM T_LEVEL
10
START WITH LEVELS = 1
11
CONNECT BY PRIOR CHILD = PARENT
12
)
13
GROUP BY SUBSTR(P, 2, 1), SUBSTR(D, 2,
1);
P
C Q
--
---------- ----------
A
E 10.8
A
Z 3
在正式的生产环境中不可能如此的死的。都是正好的，如果我把要求写成
LEVELS PARENT CHILD PARENT_QTY
CHILD_QTY
----------
---------- ---------- ---------- ----------
1 A B
1
3
2 B C
2
3
3 C D
5
6
4 D E
1
2
1 A Z
1
3
2 Z F
2
3
如果是这样的话，我想知道做一个
A用多少个E多少个F，这样你的计算方法就不适用了吧。而且正式的生产环境中也不可能用这样简单的规则来做原材料代码吧。
对于这种从中间分叉的数而言，前面的SQL确实有问题。
不过这种情况也并不复杂，只需要反相进行树形查询即可，思路仍然是一样的
SQL> SELECT *
FROM T_LEVEL;
LEVELS PARENT CHILD PARENT_QTY
CHILD_QTY
----------
---------- ---------- ---------- ----------
1 A B
1
3
2 B C
2
3
3 C D
5
6
4 D E
1
2
1 A Z
1
3
SQL> INSERT
INTO T_LEVEL VALUES (4, 'D', 'F', 1, 3);
已创建 1
行。
SQL>
SELECT
2
SUBSTR(MAX(D), LENGTH(MAX(D))) C,
3
P,
4
POWER(10, SUM(LOG(10, Q))) Q
5
FROM
6
(
7
SELECT CONNECT_BY_ROOT(CHILD)
P,
8
SYS_CONNECT_BY_PATH(PARENT,
'/') D,
9
CHILD_QTY/PARENT_QTY
Q
10
FROM T_LEVEL
11
START WITH CHILD
IN
12
(
13
SELECT CHILD
FROM
14
(
15
SELECT CHILD,
CONNECT_BY_ISLEAF LEAF FROM T_LEVEL
16
START WITH LEVELS =
1
17
CONNECT BY PRIOR CHILD =
PARENT
18
)
19
WHERE LEAF = 1
20
)
21
CONNECT BY PRIOR PARENT = CHILD
22
)
23
GROUP BY P
24
;
C
P Q
----------
---------- ----------
A
Z 3
A
E 10.8
A
F 16.2
9I的实现稍微麻烦一些：
SQL>
SELECT
2
SUBSTR(MAX(D), LENGTH(MAX(D))) C,
3
SUBSTR(P, 2, 1),
4
POWER(10, SUM(LOG(10, Q))) Q
5
FROM
6
(
7
SELECT SYS_CONNECT_BY_PATH(CHILD, '/')
P,
8
SYS_CONNECT_BY_PATH(PARENT,
'/') D,
9
CHILD_QTY/PARENT_QTY
Q
10
FROM T_LEVEL
11
START WITH CHILD
IN
12
(
13
SELECT CHILD
FROM
14
(
15
SELECT
CHILD,
16
CASE WHEN
LEAD(LEVELS) OVER(ORDER BY ID) > LEVELS THEN 0 ELSE
1 END LEAF
17
FROM
18
(
19
SELECT
ROWNUM ID, LEVELS, PARENT, CHILD
20
FROM
T_LEVEL
21
START WITH
LEVELS = 1
22
CONNECT BY
PRIOR CHILD = PARENT
23
ORDER
SIBLINGS BY LEVELS
24
)
25
)
26
WHERE LEAF = 1
27
)
28
CONNECT BY PRIOR PARENT = CHILD
29
)
30
GROUP BY SUBSTR(P, 2, 1);
C
SU Q
----------
-- ----------
A
Z 3
A
E 10.8
A
F 16.2
第二种情况也不算太难，不过现有的表结构不太合适，最好表中有唯一标识一条记录的ID列，当然如果实在没有换成ROWID也可以。
SQL> insert
into t_level values (5, 'E', 'G', 1, 1);
已创建 1
行。
SQL> SELECT *
FROM T_LEVEL
2
START WITH LEVELS =
1
3
CONNECT BY PRIOR CHILD =
PARENT
4
ORDER SIBLINGS BY CHILD;
LEVELS PARENT CHILD PARENT_QTY
CHILD_QTY
----------
---------- ---------- ---------- ----------
1 A B
1
3
2 B C
2
3
3 C D
5
6
4 D E
1
2
5 E G
1
1
4 D F
1
3
1 A Z
1
3
已选择7行。
SQL> CREATE
TABLE T_LEVEL1 AS
2
SELECT ROWNUM ID, A.* FROM T_LEVEL
A
3
START WITH LEVELS =
1
4
CONNECT BY PRIOR CHILD = PARENT
5
ORDER SIBLINGS BY LEVELS;
表已创建。
SQL> SELECT *
FROM T_LEVEL1;
ID
LEVELS PARENT
CHILD PARENT_QTY
CHILD_QTY
----------
---------- ---------- ---------- ---------- ----------
1 1 A
B 1 3
2 2 B
C 2 3
3 3 C
D 5 6
4 4 D
F 1 3
5 4 D
Z 1 4
6 4 D
E 1 2
7 1 A
Z 1 3
已选择7行。
SQL> CREATE
TABLE T_LEVEL1 AS
2
SELECT ROWNUM ID, A.* FROM T_LEVEL
A
3
START WITH LEVELS =
1
4
CONNECT BY PRIOR CHILD = PARENT
5
ORDER SIBLINGS BY LEVELS;
表已创建。
SQL> SELECT *
FROM T_LEVEL1;
ID
LEVELS PARENT
CHILD PARENT_QTY
CHILD_QTY
----------
---------- ---------- ---------- ---------- ----------
1 1 A
B 1 3
2 2 B
C 2 3
3 3 C
D 5 6
4 4 D
F 1 3
5 4 D
Z 1 4
6 4 D
E 1 2
7 1 A
Z 1 3
已选择7行。
SQL> CREATE
TABLE T_LEVEL1 AS
2
SELECT ROWNUM ID, A.* FROM T_LEVEL
A
3
START WITH LEVELS =
1
4
CONNECT BY PRIOR CHILD = PARENT
5
ORDER SIBLINGS BY LEVELS;
表已创建。
SQL> SELECT *
FROM T_LEVEL1;
ID
LEVELS PARENT
CHILD PARENT_QTY
CHILD_QTY
----------
---------- ---------- ---------- ---------- ----------
1 1 A
B 1 3
2 2 B
C 2 3
3 3 C
D 5 6
4 4 D
F 1 3
5 4 D
Z 1 4
6 4 D
E 1 2
7 1 A
Z 1 3
已选择7行。
SQL> SELECT
SUBSTR(MAX(D), LENGTH(MAX(D))) C,
2
SUBSTR(P, 2, 1),
3
POWER(10, SUM(LOG(10, Q))) Q
4
FROM
5
(
6
SELECT SYS_CONNECT_BY_PATH(ID, '/')
ID,
7
SYS_CONNECT_BY_PATH(CHILD,
'/') P,
8
SYS_CONNECT_BY_PATH(PARENT,
'/') D,
9
CHILD_QTY/PARENT_QTY
Q
10
FROM T_LEVEL1
11
START WITH ID IN
12
(
13
SELECT ID
FROM
14
(
15
SELECT
ID,
16
CASE WHEN
LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE
1 END LEAF
17
FROM
18
(
19
SELECT
ROWNUM RN, ID, LEVELS, PARENT, CHILD
20
FROM
T_LEVEL1
21
START WITH
LEVELS = 1
22
CONNECT BY
PRIOR CHILD = PARENT
23
ORDER
SIBLINGS BY LEVELS
24
)
25
)
26
WHERE LEAF = 1
27
)
28
CONNECT BY PRIOR PARENT = CHILD
29
)
30
GROUP BY SUBSTR(ID, 2, 1), SUBSTR(P, 2, 1)
;
C
SU Q
----------
-- ----------
A
Z 21.6
A
Z 3
A
F 16.2
A
E 10.8
如果需要将Z合并计算，只需要在外面再嵌套一层，sum一下即可
搭测试环境:
create table
T_T
(
T0 int
,parent varchar2(2)
,chile
varchar2(2)
,parentQty int
,childQty int
)
insert into
T_T values(1,'A','B',1,3);
insert into
T_T values(2,'B','C',2,3);
insert into
T_T values(3,'C','D',5,6);
insert into
T_T values(4,'D','E',1,2);
insert into
T_T values(1,'A','Z',1,3);
insert into
T_T values(1,'H','I',1,3);
insert into
T_T values(2,'I','J',1,3);
insert into
T_T values(3,'G','H',1,3);
insert into
T_T values(1,'L','M',1,2);
insert into
T_T values(2,'M','N',1,9);
commit;
SQL:应用lag分析函数:
select
aa.a,aa.c,aa.d,bb.h*decode(bb.i,0,1,bb.i)*decode(bb.j,0,1,bb.j)*decode(bb.k,0,1,bb.k)
FROM
(
select
B,min(parent) c,max(chile) d,A from(
select
T0,parent,chile,
SUM(p)
over(PARTITION BY A ORDER BY parent) B,A
FROM
(
select
T0,parent,chile,lag(chile) over(partition by a order by parent) C,
(case when lag(chile) over(partition by a order
by parent)=parent then 0 else 1 end )
P,B,A
from
(select
T0,parent,chile,(case ASCII (chile)-ASCII (parent) when 1 then 1
else 2 end) A,childqty/parentqty B from t_t
order by
parent)))
group by
B,A)
AA
LEFT OUTER
JOIN
(select
A,C,sum(case T0 when 1 then B else 0 end) h,
sum(case T0
when 2 then B else 0 end) i,
sum(case T0
when 3 then B else 0 end) j,
sum(case T0
when 4 then B else 0 end) k
from(
select
T0,parent,chile,B,
SUM(p)
over(PARTITION BY A ORDER BY parent) C,A
FROM
(
select
T0,parent,chile,lag(chile) over(partition by a order by parent) C,
(case when lag(chile) over(partition by a order
by parent)=parent then 0 else 1 end )
P,B,A
from
(select
T0,parent,chile,(case ASCII (chile)-ASCII (parent) when 1 then 1
else 2 end) A,childqty/parentqty B from t_t
order by
parent)))
group by
a,C) BB On aa.a||aa.b=bb.a||bb.c
BEFORE:
1
1 A B
1 3
2
1 A Z
1 3
3
2 B C
2 3
4
3 C D
5 6
5
4 D E
1 2
6
3 G H
1 3
7
1 H I
1 3
8
2 I J
1 3
9
1 L M
1 2
10
2 M N
1 9
RESULT:
1
1 A E
10.8
2
1 G J
27
3
1 L N
18
4
2 A Z
3
with
T1(PARENT,CHILD,RESULT) AS
(
SELECT
PARENT,CHILD,C_QTY/P_QTY AS REUSLT FROM BOM WHERE
PARENT='A'
UNION
ALL
SELECT
A.PARENT,A.CHILD,B.RESULT*A.C_QTY/A.P_QTY AS RESULT
FROM BOM A,T1 B
WHERE
A.PARENT=B.CHILD
)
select
CONNECT_BY_ROOT PARENT,CHILD,RESULT from
T1
WHERE
CONNECT_BY_ISLEAF=1
CONNECT BY
PRIOR CHILD=PARENT
START WITH
PARENT='A'
CREATE TABLE
BOM (PARENT VARCHAR2(10),CHILD VARCHAR2(10),P_QTY NUMBER, C_QTY
NUMBER);
INSERT INTO
BOM VALUES ('A','B',1,3);
INSERT INTO
BOM VALUES ('B','C',2,3);
INSERT INTO
BOM VALUES ('C','D',5,6);
INSERT INTO
BOM VALUES ('D','E',1,2);
INSERT INTO
BOM VALUES ('A','Z',1,3);
COMMIT;
解决思路：
1.把A的每个叶子找出来；
2.顺着叶子往根，一路作乘法上去。
SELECT
CHILD
,(SELECT
PROD_AGG(C_QTY)/PROD_AGG(P_QTY)
FROM BOM
CONNECT BY
PRIOR PARENT=CHILD
START WITH CHILD = inner.CHILD -- 从每个叶子开始
) AS
C_QTY
FROM (SELECT
BOM.*
,CONNECT_BY_ISLEAF AS IS_LEAF
FROM
BOM
CONNECT BY
PRIOR CHILD = PARENT
START WITH
PARENT='A'
) inner
WHERE
IS_LEAF=1; -- 这个条件找出所有的叶子
CHILD
C_QTY
----------
----------
E
10,8
Z
3
这个 PROD_AGG
函数比较有趣，等会新开一贴。
不用
PROD_AGG的办法：
SELECT
CHILD
,(SELECT
POWER(10,SUM(LOG(10,C_QTY)))/POWER(10,SUM(LOG(10,P_QTY)))
FROM BOM
CONNECT BY
PRIOR PARENT=CHILD
START WITH CHILD = inner.CHILD -- 从每个叶子开始
) AS
C_QTY
FROM (SELECT
BOM.*
,CONNECT_BY_ISLEAF AS IS_LEAF
FROM
BOM
CONNECT BY
PRIOR CHILD = PARENT
START WITH
PARENT='A'
) inner
WHERE
IS_LEAF=1; -- 这个条件找出所有的叶子
是的，加大难度。这次在D半成品下追加一个F，在B半成品下追加一个H，
LEVEL
Parent Child Parent Qty
Child Qty
1
A
B
1 3
2
B
C
2 3
3
C
D
5 6
4
D
E
1 2
1
A
Z
1 3
4
D
F
1 3
2
B
H
1 3
1
X
Y
1 3
2
Y
V
2 3
2
Y
Z
2 5
3
Z
M
2 5
太乱了，现在要求能够通过一个SQL，计算出做一个A，需要多少个E，Z，F，H，计算一个X，需要多少个V，M。
SELECT
CHILD
,SUM(C_QTY) AS C_QTY
--
解决叶子(原料)不唯一
FROM
(SELECT CHILD
,(SELECT
POWER(10,SUM(LOG(10,C_QTY)))/POWER(10,SUM(LOG(10,P_QTY)))
FROM
BOM
CONNECT BY PRIOR
PARENT=CHILD
START WITH CHILD = inner.CHILD
-- 从每个叶子开始
AND PARENT = inner.PARENT -- 解决叶子(原料)不唯一
) AS C_QTY
FROM
(SELECT b.*
FROM BOM b
WHERE NOT EXISTS (SELECT 1 FROM BOM WHERE parent
= b.child) --解决 9i 没有CONNECT_BY_ISLEAF
CONNECT BY PRIOR CHILD =
PARENT
START WITH PARENT='A'
)
inner
)
GROUP BY
CHILD;
ORACLE9204下测试顺利通过,结果是对的.
INSERT INTO
BOM VALUES ('A','B',1,3);
INSERT INTO
BOM VALUES ('B','C',2,3);
INSERT INTO
BOM VALUES ('C','D',5,6);
INSERT INTO
BOM VALUES ('D','E',1,2);
INSERT INTO
BOM VALUES ('A','Z',1,3);
insert into
BOM values('D','Z',1,4);
select *
from bom
SELECT
CHILD
,SUM(C_QTY) AS C_QTY
--
½â¾öÒ¶×Ó£¨Ô
ÁÏ£©²»Î¨Ò»
FROM
(SELECT CHILD
,(SELECT
POWER(10,SUM(LOG(10,C_QTY)))/POWER(10,SUM(LOG(10,P_QTY)))
FROM
BOM
CONNECT BY PRIOR
PARENT=CHILD
START WITH CHILD = inner.CHILD
--
´Óÿ¸öÒ¶×Ó¿ªÊ¼
AND PARENT = inner.PARENT --
½â¾öÒ¶×Ó£¨Ô
ÁÏ£©²»Î¨Ò»
) AS C_QTY
FROM
(SELECT b.*
FROM BOM b
WHERE NOT EXISTS (SELECT 1 FROM BOM WHERE parent
= b.child)
--½â¾ö
9i
ûÓÐCONNECT_BY_ISLEAF
CONNECT BY PRIOR CHILD =
PARENT
START WITH PARENT='A'
)
inner
)
GROUP BY
CHILD
RETURN:
E
10.8 --(3*3/2*6/5*2)
Z
24.6
--(3*3/2*6/5*4+3)
必须承认小马哥的SQL有点难懂，我斗胆来给你的最新成果挑点毛病：
Select
p,d,Sum(qty) From (
Select
p,c,d,power(10,Sum(Log(10,qty))) As qty From (
Select
Distinct P,c,SUBSTR(C,-1,1)
D,regexp_substr(c,'[^,]+',1,Level),
to_number(regexp_substr(Q,'[^*]+',1,Level)) As qty From
(
Select
CONNECT_BY_ROOT paret As P ,substr(SYS_CONNECT_BY_PATH(CHID,','),2)
As C,1||SYS_CONNECT_BY_PATH(CQTY/PQTY,'*') As Q From
tmp
Where
CONNECT_BY_ISLEAF =1
Start With
paret ='A'
Connect By
paret = Prior CHID
)
C
connect by
Level<=length(regexp_replace(Q,'[^*]',''))+1
Order By
1,2
)tt
Group
By p,c,d)ff
Group By
p,d
这个SQL里面是把CQTY/PQTY逐层拼接然后又解析出来。碰巧例子中都是可以除尽的，如果碰上一个像
2/3 的你就有误差了。不如把CQTY和PQTY分开，最后算好总乘积再相除。
你在第三行有个SUBSTR(C,-1,1) D,
其实这个就是CHID, 你最好在最里层的SELECT多写一个CHID,
然后在有用到D的地方直接引用CHID。
你在第九行有个Level<=length(regexp_replace(Q,'[^*]',''))+1,
其实右边就是最里层的LEVEL, 你最好在最里层的SELECT多写一个LEVEL AS L,
这里改为Level<= L+1.
第十行来了个排序，完全无用。
最后，我不喜欢Level<=length(regexp_replace(Q,'[^*]',''))+1这样的写法，它的连接没有用到任何PRIOR条件，前后层完全无关，只要集合里行数稍多一点，右边的表达式稍微大一点，那么这个SELECT返回的行数就呈几何级数的上涨。虽然你后来用DISTINCT过滤掉了，但中间结果的开销可不小。
杨大侠的SQL:
最后的9i版本
SELECT
SUBSTR(MAX(D), LENGTH(MAX(D))) C,
SUBSTR(P, 2,
1),
POWER(10,
SUM(LOG(10, Q))) Q
FROM
(
SELECT
SYS_CONNECT_BY_PATH(ID, '/') ID,
SYS_CONNECT_BY_PATH(CHILD, '/') P,
SYS_CONNECT_BY_PATH(PARENT, '/') D,
CHILD_QTY/PARENT_QTY Q
FROM
T_LEVEL1
START WITH
ID IN
(
SELECT ID
FROM
(
SELECT ID,
CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN)
> LEVELS THEN 0 ELSE 1 END LEAF
FROM
(
SELECT ROWNUM RN, ID, LEVELS, PARENT,
CHILD
FROM T_LEVEL1
START WITH LEVELS = 1
CONNECT BY PRIOR CHILD = PARENT
ORDER SIBLINGS BY LEVELS
)
)
WHERE LEAF
= 1
)
CONNECT BY
PRIOR PARENT = CHILD
)
GROUP BY
SUBSTR(ID, 2, 1), SUBSTR(P, 2, 1) ;
他这里用了connect
by后的ROWNUM, 利用下一个纪录层数是否增加来判断是否叶子。
这必须有一个前提，即CONNECT
BY一定是深度优先遍历的，我不知道有没有文档支持。哪天ORACLE改用广度优先，这个逻辑就不成立了。
最后，GROUP BY
SUBSTR(ID, 2, 1), SUBSTR(P, 2, 1)
有点小瑕疵，他假定ID和节点符号都只有一位字符。
如果按原料汇总，应该GROUP BY SUBSTR(P,
2, 1)才对，否则当一种原料有多个父亲的话就会有多行出现。
他的反向遍历树最后按叶子GROUP
BY还是很巧妙的。
最后，GROUP BY
SUBSTR(ID, 2, 1), SUBSTR(P, 2, 1)
有点小瑕疵，他假定ID和节点符号都只有一位字符。
如果按原料汇总，应该GROUP BY SUBSTR(P,
2, 1)才对，否则当一种原料有多个父亲的话就会有多行出现。>
这点还没有测试过,有时间再测试下.
我测试了一下，发现了问题。如果遇到公用品的话，就会出现问题：
例如下面的案例：
insert into
bom values('D','E',1,4)
insert into
bom values('A','D',1,4)
insert into
bom values('B','D',1,4)
在计算一个A用多少个D的时候，计算结果就不正确
我修改了一下，红色部分是我追加的：
SELECT CHILD
,(SELECT
POWER(10,SUM(LOG(10,C_QTY)))/POWER(10,SUM(LOG(10,P_QTY)))
FROM BOM where (parent,child)
in (
select
parent,child from bom start with parent = 'A' connect by prior
child = parent
)
CONNECT BY PRIOR
PARENT=CHILD
START WITH CHILD =
inner.CHILD
--parent = inner.parent
--如果同一个母品目下挂相同的字品目时会出现问题
比较极端的情况，但是也不能排除用户这样干。
and rowidtochar(rowid) =
inner.frowid --这个地方可以解决在同一个母品目下挂下同的字品目的问题。
) AS C_QTY
FROM
(SELECT b.*,rowidtochar(rowid) frowid
FROM BOM b
WHERE NOT EXISTS (SELECT 1 FROM BOM WHERE parent
= b.child)
START WITH PARENT in ('A')
CONNECT BY PRIOR CHILD =
PARENT
)
INNER
接上面的例子，如果用一个语句同时计算A和B各需要多少个E的话，我还是没有想到方法。
同时要求A,B的原料数，那么起点就必须有两个，而且所有数字都要按它们来分组。修改如下：
WITH vw_bom
AS (SELECT DISTINCT b.*,CONNECT_BY_ROOT(parent) AS
ROOT
FROM BOM b
CONNECT BY PRIOR CHILD =
PARENT
START WITH PARENT IN ('A','B')
)
SELECT
ROOT
,CHILD
,SUM(C_QTY) AS C_QTY
--
解决叶子(原料)不唯一
FROM
(SELECT ROOT
,CHILD
,(SELECT
POWER(10,SUM(LOG(10,C_QTY)))/POWER(10,SUM(LOG(10,P_QTY)))
FROM
vw_bom
CONNECT BY PRIOR PARENT=CHILD AND ROOT =
inner.ROOT
START WITH CHILD = inner.CHILD
-- 从每个叶子开始
AND PARENT = inner.PARENT -- 解决叶子(原料)不唯一
AND ROOT = inner.ROOT
) AS C_QTY
FROM
vw_bom inner
WHERE NOT
EXISTS (SELECT 1 FROM vw_bom WHERE parent =
inner.child)
)
GROUP BY
ROOT,CHILD;
CONNECT_BY_ROOT
在9i中可以从connect_by_path解析出来:
WITH vw_bom
AS
(SELECT DISTINCT
PARENT,CHILD,P_QTY,C_QTY,
regexp_substr(path,'[^,]+',1,1) AS ROOT --
规则表达式偷师小马哥，谢谢！不知为什么这个不能和sys_connect_by_path写在同一层,
ORACLE会报错
FROM (SELECT
b.*,sys_connect_by_path(parent,',') as
path
FROM BOM
b
CONNECT BY PRIOR CHILD =
PARENT
START WITH PARENT IN
('A','B')
)
)
..........
下面的一样
关于ORDER
SIBLINGS BY和ROWNUM, 我发现没有什么关系。用你的测试例子：
SELECT ID,
rn
FROM
(
SELECT ROWNUM RN, ID, LEVELS, PARENT,
CHILD
FROM T_LEVEL1
START WITH LEVELS = 1
CONNECT BY PRIOR CHILD = PARENT
ORDER SIBLINGS BY
LEVELS
)
最后我拿掉 ORDER
SIBLINGS BY LEVELS, 或者反序 ORDER SIBLINGS BY LEVELS DESC,
出来的顺序都是一样的。
但如果 ORDER
SIBLINGS BY ID DESC,
可以看到在取同一层节点时，是从大的ID开始，仍然是深度优先(也就是说你的判断办法仍然成立)。
所以说， ORDER
SIBLINGS BY LEVELS在这个例子中并不如你说的能保证深度优先，实际上是ORACLE自己的内部算法给了你保证。ORDER
SIBLINGS 写不写，按什么排序都不会影响你的结果。
Oracle很可能修改START
WITH的读取算法，比如对START WITH做了优化等等，这时不能保证START
WITH的读取顺序仍然和当前一致，但是根据SIBLINGS 的定义和后向兼容性的考虑，Oracle不会修改ORDER SIBLINGS
BY的排序规则。
这里添加SIBLINGS就是为了避免算法的改变而导致排序的改变，因为这个SQL依赖于这个排序的结果。
根据newkid指出的字段长度的问题修改了一下SQL：
SQL> SELECT
SUBSTR(MAX(D), INSTR(MAX(D), '/', -1) + 1) C,
2
SUBSTR(P, 2, INSTR(P, '/', 1, 2) - 2)
P,
3
POWER(10, SUM(LOG(10, Q))) Q
4
FROM
5
(
6
SELECT SYS_CONNECT_BY_PATH(ID, '/') || '/'
ID,
7
SYS_CONNECT_BY_PATH(CHILD,
'/') || '/' P,
8
SYS_CONNECT_BY_PATH(PARENT,
'/') D,
9
CHILD_QTY/PARENT_QTY
Q
10
FROM T_LEVEL1
11
START WITH ID IN
12
(
13
SELECT ID
FROM
14
(
15
SELECT
ID,
16
CASE WHEN
LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE
1 END LEAF
17
FROM
18
(
19
SELECT
ROWNUM RN, ID, LEVELS, PARENT, CHILD
20
FROM
T_LEVEL1
21
START WITH
LEVELS = 1
22
CONNECT BY
PRIOR CHILD = PARENT
23
ORDER
SIBLINGS BY LEVELS
24
)
25
)
26
WHERE LEAF = 1
27
)
28
CONNECT BY PRIOR PARENT = CHILD
29
)
30
GROUP BY SUBSTR(ID, 2, INSTR(ID, '/', 1, 2) - 2),
SUBSTR(P, 2, INSTR(P, '/', 1, 2) - 2) ;
C
P
Q
------------------------------
------------------------------ ----------
A
Z
21.6
A
Z
3
A
G
32.4
A
E
10.8
.
当然这里还要假设字段中不包含'/'
Connect
By和Start With中都追加了 ROOT = inner.ROOT 条件，为什么呢？
怎么来正确的理解Connect By和Start
With中的ROOT = inner.ROOT ？
要求起点要有A又有B,
所以这个with vw_bom as ...
里面包含了两棵树。这里面有一些数据是重复的，因为B本身是A的下级。加上了个ROOT，就可以区别这些数据是来自A或者B。相当于在这个子集合里面，(root,
parent, child) 构成了主键。
inner层把vw_bom的叶子找出来。这些叶子仍然有重复的。然后我们从每个叶子往它的根上找，这就需要三个数据来从vw_bom里面定位记录：(root,
parent, child)
start with
带个 ROOT = inner.ROOT 就是为了确保是同一个叶子；CONNECT BY
带上这条件是为了保证是同一棵树。
刚才想起来9i可能不支持规则表达式，那你就自己用instr,
substr代替吧，很简单的。
siblings是指同层次的兄弟节点，order
siblings by
的作用是：当有多个兄弟可选时，先访问哪一个。它并不能决定是“先访问同层节点”还是“先访问下级节点”(而这个才是你判断的依据)。
假设有这么个树：
A
BCD
EFG
假设第二三层的点是一一对应的。
访问完A之后，BCD中先选哪一个？那就是order
siblings by起作用的地方。如果是order siblings by node_id,
顺序就是：ABECFDG;如果是order siblings by node_id DESC,
顺序就是：ADGCFBE
ORACLE现在的做法，它每个路径都会走到底才回溯,
也就是深度优先的。如果哪天ORACLE心血来潮换成广度优先，顺序就变成：
order
siblings by node_id: ABCDEFG
order
siblings by node_id DESC: ADCBGFE
在这种情况下你的判断方法就失效了。
如果你说的order
siblings by能起作用，如何解释把顺序反过来以后依然成立呢？
ps:我找到oracle的官方文档看了，深度优先顺序是有保证的，但和order
siblings by无关，你完全可以把它拿掉，结果依然正确。
树形查询的执行计划决定了Oracle采用深度的方式遍历树。
至于SBILINGS确实没有必要
3075
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
