表名 test
字段和记录
id name pid
1 爷爷 0
2 爸爸 1
3 儿子 2
------------------------------------------
要求结果是:爷爷/爸爸/儿子不知道用sql能不能实现?
字段和记录
id name pid
1 爷爷 0
2 爸爸 1
3 儿子 2
------------------------------------------
要求结果是:爷爷/爸爸/儿子不知道用sql能不能实现?
LTRIM(MAX(SYS_CONNECT_BY_PATH(ENAME,
',')) KEEP(DENSE_RANK LAST ORDER BY CURR),
',') AS CONCATENATED
FROM (SELECT DEPTNO,
ENAME,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ENAME) AS CURR,
ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ENAME) - 1 AS PREV
FROM EMP)
GROUP BY DEPTNO
CONNECT BY PREV = PRIOR CURR
AND DEPTNO = PRIOR DEPTNO
START WITH CURR = 1;
start with id=1
connect by prior id = pid这样就可以得到楼主想要的。
或
select name from test start with pid=0connect by prior pid=id