name id pid
X 1 0
T 2 0
C 3 0
L 4 1
P 5 4
W 6 2
I 7 4
O 8 2
Y 9 6
M 10 7
------------------要求一个sql达到如下选择结果
X L P
X L I
X L I M
T W
T W Y
T O
C----------------------
难度在于列数是不确定的,即,父子关系的层数是不确定的。
X 1 0
T 2 0
C 3 0
L 4 1
P 5 4
W 6 2
I 7 4
O 8 2
Y 9 6
M 10 7
------------------要求一个sql达到如下选择结果
X L P
X L I
X L I M
T W
T W Y
T O
C----------------------
难度在于列数是不确定的,即,父子关系的层数是不确定的。
解决方案 »
- left join的一个奇怪问题
- SQLPLUS中导出长字符串格式的数据怎么处理
- Oracle里是否存在与SQL SERVER 2000数据库一样的功能,即怎么知道连接后所有用户对该数据库所执行的语句操作,在SQL SERVER 2000里好象是叫
- 多次访问数据库快,还是一次访问数据库快
- 如何通过数据字典查询在建表时对表设的主键、外键、唯一键和check等信息啊?
- 问题真难,高手来帮助一下吧!送1000分都可以。
- 问oracle的几个题目
- 如何使字段自动增加?
- 高手请进!!!绝对难题
- 请问在SQL语句中的"或"="or"的语法是什么?(对不起,没分了)
- 外部表在Oracle数据库中使用心得
- oracle 10g创建数据库
(select level as le, sys_connect_by_path(name,',') as path from tb110 t
start with t.pid=0
connect by prior t.id= t.pid
)得到如下..
SUBSTR(PATH,2)
X
X, L
X, L, P
X, L, I
X, L, I , M
T
T, W
T, W, Y
T, O
C不知道你什么规则过滤出来,你列出来的结果?
X L I M
T W Y
T O
C
------------
好,结果要以上的,规则是叶子节点放在最右边。
左边一列只能出现PID=0的
左边一列只能出现PID=0的汗...就C,就不满足你上面说的了....
IF OBJECT_ID('[t1]') IS NOT NULL
DROP TABLE [t1]
GO
CREATE TABLE [t1] ([name] [nvarchar](10),[id] [int],[pid] [int])
INSERT INTO [t1]
SELECT 'X','1','0' UNION ALL
SELECT 'T','2','0' UNION ALL
SELECT 'C','3','0' UNION ALL
SELECT 'L','4','1' UNION ALL
SELECT 'P','5','4' UNION ALL
SELECT 'W','6','2' UNION ALL
SELECT 'I','7','4' UNION ALL
SELECT 'O','8','2' UNION ALL
SELECT 'Y','9','6' UNION ALL
SELECT 'M','10','7'--SELECT * FROM [t1]-->SQL查询如下:
;WITH t AS
(
SELECT name1 = CAST(name AS VARCHAR),*, px = CAST(id AS VARBINARY)
FROM t1
WHERE pid = 0
UNION ALL
SELECT CAST(name1+' '+a.name AS VARCHAR), a.*,
CAST(px+CAST(a.id AS VARBINARY) AS VARBINARY)
FROM t1 a
JOIN t b
ON a.pid = b.id
)
SELECT name=name1
FROM t a
WHERE NOT EXISTS(
SELECT 1
FROM t WHERE a.id=pid)
ORDER BY px
/*
name
------------------------------
X L P
X L I M
T W Y
T O
C(5 行受影响)
*/
(select 'X' name , '1' id , '0' pid from dual union all
select 'T', '2', '0' from dual union all
select 'C', '3', '0' from dual union all
select 'L', '4', '1' from dual union all
select 'P', '5', '4' from dual union all
select 'W', '6', '2' from dual union all
select 'I', '7', '4' from dual union all
select 'O', '8', '2' from dual union all
select 'Y', '9', '6' from dual union all
select 'M', '10', '7' from dual )
Select path From (
Select sys_connect_by_path(Name,' ') path ,CONNECT_BY_ISLEAF As IsLeaf From t
Start With pid = '0'
Connect By Prior Id = pid) t1
Where t1.IsLeaf = 1
不是选出是多列么?
select str from
(select substr(sys_connect_by_path(name,','),2) str ,CONNECT_BY_ISLEAF IsLeaf
from tab
start with pid = 0
connect by prior id = pid)
where IsLeaf = 1;