ORACLE中数据递归查询遍历sql语句
Jun 07, 2016 pm 05:45 PM查询遍历,需要在将数据在基表中按照层次结构进行存储。比如一个组织机构就是这样的典型例子:
实现语句:
select column
from table_name
start with column=value
connect by prior 父主键=子外键
例1:在oracle的emp 表中,每一条记录都有一个唯一标识当前雇员的empno和标识这个雇员的经理的mgr列。如果mgr 为空,则该雇员是该机构的最顶级。现在要列出每个雇员的层次结构(从顶到底):
select lpad(' ',4*(level-1))||ename name ,empno,mgr from emp
start with mgr is null
connect by prior empno=mgr;
name empno mgr
-------------------- --------- ---------
king 7839
jones 7566 7839
scott 7788 7566
adams 7876 7788
ford 7902 7566
smith 7369 7902
blake 7698 7839
allen 7499 7698
ward 7521 7698
martin 7654 7698
turner 7844 7698
james 7900 7698
clark 7782 7839
miller 7934 7782
14 rows selected.
sql>
从查询结果中可以看出,由于jones、blake、clark的上司是king,所以jones等mgr(经理编号)=king的empno号,即king的直接下级是jones、blake、clark,因为他们的mgr与king的empno一样。

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

How long will Oracle database logs be kept?

The order of the oracle database startup steps is

Oracle database server hardware configuration requirements

How to see the number of occurrences of a certain character in Oracle

How much memory is needed to use oracle database

Oracle scheduled tasks execute the creation step once a day

70B model generates 1,000 tokens in seconds, code rewriting surpasses GPT-4o, from the Cursor team, a code artifact invested by OpenAI
