Home Database Mysql Tutorial ORACLE中数据递归查询遍历sql语句

ORACLE中数据递归查询遍历sql语句

Jun 07, 2016 pm 05:45 PM
oracle sql data Inquire recursion Traverse

查询遍历,需要在将数据在基表中按照层次结构进行存储。比如一个组织机构就是这样的典型例子:

  实现语句:

 

 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一样。

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How long will Oracle database logs be kept? How long will Oracle database logs be kept? May 10, 2024 am 03:27 AM

How long will Oracle database logs be kept?

The order of the oracle database startup steps is The order of the oracle database startup steps is May 10, 2024 am 01:48 AM

The order of the oracle database startup steps is

Oracle database server hardware configuration requirements Oracle database server hardware configuration requirements May 10, 2024 am 04:00 AM

Oracle database server hardware configuration requirements

How to see the number of occurrences of a certain character in Oracle How to see the number of occurrences of a certain character in Oracle May 09, 2024 pm 09:33 PM

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

How much memory does oracle require? How much memory does oracle require? May 10, 2024 am 04:12 AM

How much memory does oracle require?

How much memory is needed to use oracle database How much memory is needed to use oracle database May 10, 2024 am 03:42 AM

How much memory is needed to use oracle database

Oracle scheduled tasks execute the creation step once a day Oracle scheduled tasks execute the creation step once a day May 10, 2024 am 03:03 AM

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 70B model generates 1,000 tokens in seconds, code rewriting surpasses GPT-4o, from the Cursor team, a code artifact invested by OpenAI Jun 13, 2024 pm 03:47 PM

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

See all articles