数据表 demo
字段 id pid name
1 null p1
2 1 p2
3 null p3
4 3 p4
5 4 p5求返回结果 id pid name pname
1 null p1 p1
2 1 p2 p1,p2
3 null p3 p3
4 3 p4 p3,p4
5 4 p5 p3,p4,p5请问能返回这样的结果的查询语句该怎么写?如下是我已经能返回结果的查询,里面用了个函数实现了,如果不用函数,能实现吗,该怎么写?函数:
CREATE OR REPLACE FUNCTION GET_PATHNAME(i_id in number)
RETURN varchar2 AS
o_ret varchar2(4000):='';
BEGIN
SELECT wmsys.wm_concat(NAME) INTO o_ret FROM
(
SELECT to_char(NAME) as NAME
FROM DEMO
START WITH ID=i_id
CONNECT BY PRIOR PID=ID
ORDER BY ID
);
RETURN o_ret;
END GET_PATHNAME;下面是调用这个函数得到上述结果的命令
select get_pathname(t.ID) as pname,t.* from DEMO t order by ID需求的是不调用这个函数,直接在一个查询命令实现返回结果。请高手指点,谢谢。
字段 id pid name
1 null p1
2 1 p2
3 null p3
4 3 p4
5 4 p5求返回结果 id pid name pname
1 null p1 p1
2 1 p2 p1,p2
3 null p3 p3
4 3 p4 p3,p4
5 4 p5 p3,p4,p5请问能返回这样的结果的查询语句该怎么写?如下是我已经能返回结果的查询,里面用了个函数实现了,如果不用函数,能实现吗,该怎么写?函数:
CREATE OR REPLACE FUNCTION GET_PATHNAME(i_id in number)
RETURN varchar2 AS
o_ret varchar2(4000):='';
BEGIN
SELECT wmsys.wm_concat(NAME) INTO o_ret FROM
(
SELECT to_char(NAME) as NAME
FROM DEMO
START WITH ID=i_id
CONNECT BY PRIOR PID=ID
ORDER BY ID
);
RETURN o_ret;
END GET_PATHNAME;下面是调用这个函数得到上述结果的命令
select get_pathname(t.ID) as pname,t.* from DEMO t order by ID需求的是不调用这个函数,直接在一个查询命令实现返回结果。请高手指点,谢谢。
FROM demo d
START WITH pid is null
CONNECT BY PRIOR id = pid;
ID PID NAME
---------- ---------- --------------------
1 p1
2 1 p2
3 p3
4 3 p4
5 4 p5
SQL>
SQL> SELECT d.*,ltrim(SYS_CONNECT_BY_PATH(name, ','),',') "pname"
2 FROM demo d
3 START WITH pid is null
4 CONNECT BY PRIOR id = pid;
ID PID NAME pname
---------- ---------- -------------------- --------------------------------------------------------------------------------
1 p1 p1
2 1 p2 p1,p2
3 p3 p3
4 3 p4 p3,p4
5 4 p5 p3,p4,p5
nvl2(t.pid,
(select wmsys.wm_concat(name) from a
start with a.id=7
connect by prior a.pid=a.id ),t.name)
from demo t使用ORACLE 10G以上版本,因为使用了函数wmsys.wm_concat(name),试试,我本机的9I的无法测试...