我有一个表结构BOM_Table,结构如下:
PARENT_CD CHILD_CD
A B
B C
C D
B E
E F
H B
H I
B G
A --- B --- C --- D
--- E --- F
--- G
H --- I
B --- G
--- C --- D
--- E --- F如何用SQL语句查出:E\D\G等最上层的结构是A和H?谢谢~~
PARENT_CD CHILD_CD
A B
B C
C D
B E
E F
H B
H I
B G
A --- B --- C --- D
--- E --- F
--- G
H --- I
B --- G
--- C --- D
--- E --- F如何用SQL语句查出:E\D\G等最上层的结构是A和H?谢谢~~
select distinct parent_cd
from BOM_Table a
left join BOM_Table b
on (a.parent_cd=b.child_cd)
where b.parent_cd is null
from BOM_Table a
left join BOM_Table b
on (a.parent_cd=b.child_cd)
where b.parent_cd is null
(select 'A' a, 'B' b from dual
union all
select 'B' a, 'C' b from dual
union all
select 'C' a, 'D' b from dual
union all
select 'B' a, 'E' b from dual
union all
select 'E' a, 'F' b from dual
union all
select 'H' a, 'B' b from dual
union all
select 'H' a, 'I' b from dual
union all
select 'B' a, 'G' b from dual
)
select distinct a.a
from T a
left join T b
on (a.a=b.b)
where b.b is null
from BOM_Table a,BOM_Table b
where level=1
start with a.parent_cd is null
connect by a.parent_cd=prior b.child_cd
from (select distinct T.parent_id par from T) as P
where p.par not in (select distinct T.child_id from T);
connect by 不是分析函数,请勿混淆。
WITH T AS (
SELECT 'A' PARENT_CD, 'B' CHILD_CD FROM DUAL UNION
SELECT 'B', 'C' FROM DUAL UNION
SELECT 'C', 'D' FROM DUAL UNION
SELECT 'B', 'E' FROM DUAL UNION
SELECT 'E', 'F' FROM DUAL UNION
SELECT 'H', 'B' FROM DUAL UNION
SELECT 'H', 'I' FROM DUAL UNION
SELECT 'B', 'G' FROM DUAL )
-- 上面是测试数据,下面的才是查询用SQL,引用时不需要包含上面的代码
SELECT DISTINCT *
FROM T
WHERE CONNECT_BY_ISLEAF = 1
START WITH CHILD_CD = 'E' OR CHILD_CD = 'D' OR CHILD_CD = 'G'
CONNECT BY PRIOR PARENT_CD = CHILD_CD;
首先我有一个Table A,结构如下:CODE GOODS
C-1 E
C-1 D
C-1 G
C-2 F
C-2 B表结构BOM_Table,结构如下:
PARENT_CD CHILD_CD
A B
B C
C D
B E
E F
H B
H I
B G我输入的查询条件是C-1,这时在Table_A中CODE = C-1的GOODS有E\D\G,
我希望从BOM_Table中查到在E\D\G的最上层。
SELECT DISTINCT *
FROM BOM_Table
WHERE CONNECT_BY_ISLEAF = 1
START WITH CHILD_CD IN (SELECT GOODS FROM TABLEA WHERE CODE = 'C-1')
CONNECT BY PRIOR PARENT_CD = CHILD_CD;
CONNECT_BY_ISLEAF 这个是什么意思呢?感谢