SELECT * FROM T
START WITH FU='A'
CONNECT BY PRIOR ZI = FU;递归查询
START WITH FU='A'
CONNECT BY PRIOR ZI = FU;递归查询
解决方案 »
- Oracle PL/SQL转VB.NET或者Java
- Oracle中日期类型的问题
- 讨论:db2 和 oracle 各自的优点和缺点
- 如何执行字段中的sql语句?
- ASP调用oracle存储过程问题,提示错误
- oracle问题急需解决!在线等!
- 我的机器上装了oracle8.17,现在平时不怎么用了,但每次开机时oracle.exe这个进程还是会自动启动,我想让它不在在开机时自动运行该怎么办?
- 可不可以返回2個游標變量???
- 一个比较大的系统,不管什么字段数字类型值,日期都定义成VARCHAR2类型,这样会有什么不妥吗
- 创建数据库的问题
- oracle数据替换的相关问题,请大神支援~~
- Oracle 客户机与服务器无法正常查询数据
(SELECT 'A' ID, 'A1' PID
FROM DUAL
UNION ALL
SELECT 'A' ID, 'A2' PID
FROM DUAL
UNION ALL
SELECT 'A1' ID, 'A11' PID
FROM DUAL
UNION ALL
SELECT 'A1' ID, 'A12' PID
FROM DUAL
UNION ALL
SELECT 'A11' ID, 'A111' PID
FROM DUAL
UNION ALL
SELECT 'A2' ID, 'A21' PID
FROM DUAL
UNION ALL
SELECT 'A2' ID, 'A22' PID
FROM DUAL)
SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(RN, '-')), '-') COL, T2.ID, T2.PID
FROM (SELECT T1.*,
ROW_NUMBER() OVER(PARTITION BY C_LEVEL, ID ORDER BY PID) AS RN
FROM (SELECT T.ID, T.PID, MAX(LEVEL) C_LEVEL
FROM TEST T
CONNECT BY ID = PRIOR PID
GROUP BY T.ID, T.PID) T1) T2
START WITH C_LEVEL = 1
CONNECT BY ID = PRIOR PID
GROUP BY T2.ID, T2.PID, T2.RN
ORDER BY LTRIM(MAX(SYS_CONNECT_BY_PATH(RN, '-')), '-')
;
2 UNION ALL SELECT 'A' ID, 'A2' PID FROM DUAL
3 UNION ALL SELECT 'A1' ID, 'A11' PID FROM DUAL
4 UNION ALL SELECT 'A1' ID, 'A12' PID FROM DUAL
5 UNION ALL SELECT 'A11' ID, 'A111' PID FROM DUAL
6 UNION ALL SELECT 'A2' ID, 'A21' PID FROM DUAL
7 UNION ALL SELECT 'A2' ID, 'A22' PID FROM DUAL)
8 select * from test start with id='A' connect by prior pid=id;ID PID
--- ----
A A1
A1 A11
A11 A111
A1 A12
A A2
A2 A21
A2 A22