当前位置：首页 → 问答吧 → SQL请教

SQL请教

时间：2011-11-24

来源：互联网

表结构如下：
PARENT CHILD
A B
B C
A D
D F
B E

要查找所有不在最下层的以及它所在的层次。
如：
A 1
B 2
C 3
E 3
D 2
F 3

作者: netusealways 发布时间: 2011-11-24

该回复于2011-11-24 13:38:46被管理员删除

对我有用[0]
丢个板砖[0]
引用
举报
管理
TOP

cosio
等　级：

#2楼得分：0回复于：2011-11-24 14:00:46

SQL code


--SQL:
WITH T AS(
SELECT 
'A' a,'B' b FROM dual
UNION ALL
SELECT 
'B','C' FROM dual
UNION ALL
SELECT 
'A','D' FROM dual
UNION ALL
SELECT 
'D','F' FROM dual
UNION ALL
SELECT 
'B','E' FROM dual
)

SELECT DISTINCT a1.a,nvl(rn,a3.rm) FROM
(
  SELECT a FROM t
  UNION
  SELECT b FROM t
) a1,
(SELECT a,LEVEL rn FROM t 
START WITH a='A' 
CONNECT BY a=PRIOR b) a2,
(SELECT max(LEVEL)+1 rm FROM t 
START WITH a='A' 
CONNECT BY a=PRIOR b) a3
WHERE a1.a=a2.a(+)

--result:

A    1
B    2
C    3
D    2
E    3
F    3

作者: lsqldfdfd 发布时间: 2011-11-24

仅供参考
SQL code

with t as (
select 'a'  parent,'b' child from dual union all
select 'b' parent,'c'  child from dual union all
select 'a' parent,'d'  child from dual union all
select 'd' parent,'f' child from dual union all
select 'b' parent,'e' child from dual 
)
select distinct t.parent,level  from t 
start with t.parent='a'
connect by prior child=parent;

作者: cosio 发布时间: 2011-11-24

SQL code

WITH a AS (SELECT LEVEL lev,parent_id,child_id FROM lev START WITH parent_id='A' CONNECT BY parent_id=PRIOR child_id)
SELECT  lev,parent_id ID FROM a
UNION
SELECT  lev+1 lev,child_id ID FROM a
ORDER BY ID

作者: dws2004 发布时间: 2011-11-24

是指表中所有LEVEL都是1的，都需要查询出来。不仅仅是A

作者: youshang444 发布时间: 2011-11-24

SQL请教

热门阅读

热门下载