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----------------------
难度在于列数是不确定的,即,父子关系的层数是不确定的。
解决方案 »
- 问个sql语句
- 本人初学oracle 不清楚 sequser.table_seq.nextval
- oracle10g版本是否不允许用数据字典模式管理表空间?
- 求救,oracle中执行"col column_name format a20" 命令后,查询该字段的值为“##########”,怎么解决,恢复的方法??
- 输入值对于日期格式不够长 问题
- 举例说明什么是参数表?
- 一个关于decode中字符串比较的问题。
- 怎么建一个视图提取最近3个月的数据
- 数据库里可以用sql语句判断是否存在某个表么?
- 关于两个oracle数据库中方案的导入导出问题
- 外部表在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;