给你一个例子看看:
SELECT DISTINCT A.USERID,B.DWBH00,C.DWMC00 FROM SYSUSER_USERLIST A,SYSUSER_ZGYQXB B ,DA_DWXX00 C WHERE A.USERID=B.USERID AND B.DWBH00=C.DWBH00 AND A.FZXBH0=C.FZXBH0 AND B.USERID IN (SELECT USERID FROM SYSUSER_USERLIST CONNECT BY USERID=PRIOR USERGR START WITH USERID=:TMP) ORDER BY USERID
create table testtree(id int,priorid int,name varchar2(20));
insert into testtree values(1,0,'中国a');
insert into testtree values(2,0,'美国');
insert into testtree values(3,0,'加拿大');
insert into testtree values(4,1,'北京');
insert into testtree values(5,1,'上海');
insert into testtree values(6,1,'江苏');
insert into testtree values(7,6,'苏州');
insert into testtree values(8,7,'常熟');
insert into testtree values(9,6,'南京');
insert into testtree values(10,6,'无锡');
insert into testtree values(11,2,'纽约');
insert into testtree values(12,2,'旧金山');
----全显示-----
select lpad(' ',level-1,' ')||name name from testtree a start with priorid=0 connect by prior id = priorid;