oracle树形结构层次查询

king

|

-------------------------------------------------------------------------------

| | jones blake clark

| | |

---------------- -------------- | |

scott ford

| |

ad s

[root@server1 ~]# su - oracle

milfr

|

[oracle@server1 ~]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 8 13:54:46 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SCOTT>select ename,sal from emp e,(select deptno,avg(sal) avg_sal from emp group by deptno) s where e.deptno=s.deptno;

ENAME SAL

---------- ----------

SMITH 800

ALLEN 1600

WARD 1250

JONES 2975

MARTIN 1250

BLAKE 2850

CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 rows selected.

2450

3000

1500

1100

950

3000

5000

1300

SCOTT>select ename,sal from emp e,(select deptno,avg(sal) avg_sal from emp group by deptno) s where e.deptno=s.deptno and e,sal>s.avg_sal;

select ename,sal from emp e,(select deptno,avg(sal) avg_sal from emp group by deptno) s where e.deptno=s.deptno and e,sal>s.avg_sal

*

ERROR at line 1:

ORA-00920: invalid relational operator

SCOTT>select ename,sal from emp e,(select deptno,avg(sal) avg_sal from emp group by deptno) s where e.deptno=s.deptno and e.sal>s.avg_sal;

ENAME SAL

---------- ----------

ALLEN 1600

JONES 2975

BLAKE 2850

SCOTT 3000

KING 5000

FORD 3000

6 rows selected.

SCOTT>ed

Wrote file afiedt.buf

1 select ename,sal from emp e,

2 (select deptno,avg(sal) avg_sal from emp group by deptno) s

3* where e.deptno=s.deptno and e.sal>s.avg_sal

SCOTT>

SCOTT>

SCOTT>

SCOTT>

SCOTT>

SCOTT>

SCOTT>

SCOTT>select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250

500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN 1400 30

7698 BLAKE 30

7782 CLARK 10

7788 SCOTT 20

7839 KING 10

7844 TURNER 0 30

7876 ADAMS SALESMAN MANAGER ANALYST PRESIDENT SALESMAN CLERK 7698 28-SEP-81 7839 01-MAY-81 7839 09-JUN-81 7566 19-APR-87 17-NOV-81 7698 08-SEP-81 7788 23-MAY-87 1250 2850 2450 3000 5000 1500 1100 MANAGER

20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SCOTT>select ename from emp start with ename='KING' connect by prior empno=mgr;

ENAME

----------

KING

JONES

SCOTT

ADAMS

FORD

SMITH

BLAKE

ALLEN

WARD

MARTIN

TURNER

JAMES

CLARK

MILLER

14 rows selected.

SCOTT>select level,ename from emp start with ename='KING' connect by prior empno=mgr;

LEVEL ENAME

---------- ----------

1 KING

2 JONES

3 SCOTT

4 ADAMS

3 FORD

4 SMITH

2 BLAKE

3 ALLEN

3 WARD

3 MARTIN

3 TURNER

3 JAMES

2 CLARK

3 MILLER

14 rows selected.

SCOTT>select level,lpad(ename,length(ename)+2*level-2,'_') from emp start with ename='KING' connect by prior empno=mgr;

LEVEL

----------

LPAD(ENAME,LENGTH(ENAME)+2*LEVEL-2,'_')

-------------------------------------------------------------------------------------

-----------------------------------

1

KING

2

__JONES

3

____SCOTT

4

______ADAMS

3

____FORD

4

______SMITH

2

__BLAKE

3

____ALLEN

3

____WARD

3

____MARTIN

3

____TURNER

3

____JAMES

2

__CLARK

3

____MILLER

14 rows selected.

SCOTT>select level,lpad(ename,length(ename)+2*level-2,'_') chgfrom emp start with ename='KING' connect by prior empno=mgr;

select level,lpad(ename,length(ename)+2*level-2,'_') chgfrom emp start with ename='KING' connect by prior empno=mgr

*

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

SCOTT>select level,lpad(ename,length(ename)+2*level-2,'_') chg from emp start with ename='KING' connect by prior empno=mgr;

LEVEL

----------

CHG

------------------------------------------------------------------------------------------------------------------------

1

KING

2

__JONES

3

____SCOTT

4

______ADAMS

3

____FORD

4

______SMITH

2

__BLAKE

3

____ALLEN

3

____WARD

3

____MARTIN

3

____TURNER

3

____JAMES

2

__CLARK

3

____MILLER

14 rows selected.

SCOTT>col chg for a20

SCOTT>/

LEVEL CHG

---------- --------------------

1 KING

2 __JONES

3 ____SCOTT

4 ______ADAMS

3 ____FORD

4 ______SMITH

2 __BLAKE

3 ____ALLEN

3 ____WARD

3 ____MARTIN

3 ____TURNER

3 ____JAMES

2 __CLARK

3 ____MILLER

14 rows selected.

SCOTT>select level,lpad(ename,length(ename)+2*level-2,'_') chg from emp start with ename='SCOTT' connect by prior empno=mgr;

LEVEL CHG

---------- --------------------

1 SCOTT

2 __ADAMS

SCOTT>select level,lpad(ename,length(ename)+2*level-2,'_') chg from emp

start with ename='JONES' connect by prior empno=mgr;

LEVEL CHG

---------- --------------------

1 JONES

2 __SCOTT

3 ____ADAMS

2 __FORD

3 ____SMITH

SCOTT>select level,lpad(ename,length(ename)+2*level-2,'_') chg from emp start with ename='SCOTT' connect by prior mgr=empno;

LEVEL CHG

---------- --------------------

1 SCOTT

2 __JONES

3 ____KING

SCOTT>select level,lpad(ename,length(ename)+2*level-2,'_') chg from emp start with ename='SCOTT' connect by empno=mgr proir;

select level,lpad(ename,length(ename)+2*level-2,'_') chg from emp start with ename='SCOTT' connect by empno=mgr proir

*

ERROR at line 1:

ORA-00933: SQL command not properly ended

SCOTT>select level,lpad(ename,length(ename)+2*level-2,'_') chg from emp start with ename='SCOTT' connect by empno= proir mgr;

select level,lpad(ename,length(ename)+2*level-2,'_') chg from emp start with ename='SCOTT' connect by empno= proir mgr

*

ERROR at line 1:

ORA-00933: SQL command not properly ended

SCOTT>select level,lpad(ename,length(ename)+2*level-2,'_') chg from emp start with ename='SCOTT' connect by empno= prior