Oracle10g中层次查询简介-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

Oracle10g中层次查询简介

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:15 PM

stalevelusInquireIntroduction

我们可以通过START WITH . . . CONNECT BY . . .子句来实现SQL的层次查询，而Oracle 10g 为其添加许多了新的伪列。十多年以来，Oracle SQL 具有依照层次关系进行查询的功能。例如，你可以指定一个起始条件，然后根据一个或多个连接条件来确定孩子行的内容

　　我们可以通过START WITH . . . CONNECT BY . . .子句来实现SQL的层次查询，而Oracle 10g 为其添加许多了新的伪列。十多年以来，Oracle SQL 具有依照层次关系进行查询的功能。例如，你可以指定一个起始条件，然后根据一个或多个连接条件来确定孩子行的内容。举例来说，现在假设我有一个表，里面记录了世界上的某些地区，其表结构如下：

create table hier<br><br>(<br><br>parent varchar2(30),<br><br>child varchar2(30)<br><br>);<br><br>insert into hier values(null,'Asia');<br><br>insert into hier values(null,'Australia');<br><br>insert into hier values(null,'Europe');<br><br>insert into hier values(null,'North America');<br><br>insert into hier values('Asia','China');<br><br>insert into hier values('Asia','Japan');<br><br>insert into hier values('Australia','New South Wales');<br><br>insert into hier values('New South Wales','Sydney');<br><br>insert into hier values('California','Redwood Shores');<br><br>insert into hier values('Canada','Ontario');<br><br>insert into hier values('China','Beijing');<br><br>insert into hier values('England','London');<br><br>insert into hier values('Europe','United Kingdom');<br><br>insert into hier values('Japan','Osaka');<br><br>insert into hier values('Japan','Tokyo');<br><br>insert into hier values('North America','Canada');<br><br>insert into hier values('North America','USA');<br><br>insert into hier values('Ontario','Ottawa');<br><br>insert into hier values('Ontario','Toronto');<br><br>insert into hier values('USA','California');<br><br>insert into hier values('United Kingdom','England');

　　那么我们可以使用START WITH . . . CONNECT BY . . .从句将父级地区与孩子地区连接起来，并将其层次等级显示出来。

column child format a40<br><br>select level,lpad(' ',level*3)||child child<br><br>from hier<br><br>start with parent is null<br><br>connect by prior child = parent;<br><br>LEVEL CHILD<br><br>---------- --------------------------<br><br>1 Asia<br><br>2 China<br><br>3 Beijing<br><br>2 Japan<br><br>3 Osaka<br><br>3 Tokyo<br><br>1 Australia<br><br>2 New South Wales<br><br>3 Sydney<br><br>1 Europe<br><br>2 United Kingdom<br><br>3 England<br><br>4 London<br><br>1 North America<br><br>2 Canada<br><br>3 Ontario<br><br>4 Ottawa<br><br>4 Toronto<br><br>2 USA<br><br>3 California<br><br>4 Redwood Shores

自从Since Oracle 9i 开始，就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。如下例所示：

column path format a50<br><br>select level,sys_connect_by_path(child,'/') path<br><br>from hier<br><br>start with parent is null<br><br>connect by prior child = parent;<br><br>LEVEL PATH <br><br>-------- --------------------------------------------<br><br>1 /Asia<br><br>2 /Asia/China<br><br>3 /Asia/China/Beijing<br><br>2 /Asia/Japan<br><br>3 /Asia/Japan/Osaka<br><br>3 /Asia/Japan/Tokyo<br><br>1 /Australia<br><br>2 /Australia/New South Wales<br><br>3 /Australia/New South Wales/Sydney<br><br>1 /Europe<br><br>2 /Europe/United Kingdom<br><br>3 /Europe/United Kingdom/England<br><br>4 /Europe/United Kingdom/England/London<br><br>1 /North America<br><br>2 /North America/Canada<br><br>3 /North America/Canada/Ontario<br><br>4 /North America/Canada/Ontario/Ottawa<br><br>4 /North America/Canada/Ontario/Toronto<br><br>2 /North America/USA<br><br>3 /North America/USA/California<br><br>4 /North America/USA/California/Redwood Shores

在 Oracle 10g 中，还有其他更多关于层次查询的新特性。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。下给出了一个关于这个函数使用的例子：

select connect_by_isleaf,sys_connect_by_path(child,'/') path<br><br>from hier<br><br>start with parent is null<br><br>connect by prior child = parent;<br><br>CONNECT_BY_ISLEAF PATH<br><br>----------------------------------

0 /Asia<br><br>0 /Asia/China<br><br>1 /Asia/China/Beijing<br><br>0 /Asia/Japan<br><br>1 /Asia/Japan/Osaka<br><br>1 /Asia/Japan/Tokyo<br><br>0 /Australia<br><br>0 /Australia/New South Wales<br><br>1 /Australia/New South Wales/Sydney<br><br>0 /Europe<br><br>0 /Europe/United Kingdom<br><br>0 /Europe/United Kingdom/England<br><br>1 /Europe/United Kingdom/England/London<br><br>0 /North America<br><br>0 /North America/Canada<br><br>0 /North America/Canada/Ontario<br><br>1 /North America/Canada/Ontario/Ottawa<br><br>1 /North America/Canada/Ontario/Toronto<br><br>0 /North America/USA<br><br>0 /North America/USA/California<br><br>1 /North America/USA/California/Redwood Shores

Statement

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

Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

MySQL: How to Add a User RemotelyMay 12, 2025 am 12:10 AM

ToaddauserremotelytoMySQL,followthesesteps:1)ConnecttoMySQLasroot,2)Createanewuserwithremoteaccess,3)Grantnecessaryprivileges,and4)Flushprivileges.BecautiousofsecurityrisksbylimitingprivilegesandaccesstospecificIPs,ensuringstrongpasswords,andmonitori

The Ultimate Guide to MySQL String Data Types: Efficient Data StorageMay 12, 2025 am 12:05 AM

TostorestringsefficientlyinMySQL,choosetherightdatatypebasedonyourneeds:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseTEXTforlong-formtextcontent.4)UseBLOBforbinarydatalikeimages.Considerstorageov

MySQL BLOB vs. TEXT: Choosing the Right Data Type for Large ObjectsMay 11, 2025 am 12:13 AM

When selecting MySQL's BLOB and TEXT data types, BLOB is suitable for storing binary data, and TEXT is suitable for storing text data. 1) BLOB is suitable for binary data such as pictures and audio, 2) TEXT is suitable for text data such as articles and comments. When choosing, data properties and performance optimization must be considered.

MySQL: Should I use root user for my product?May 11, 2025 am 12:11 AM

No,youshouldnotusetherootuserinMySQLforyourproduct.Instead,createspecificuserswithlimitedprivilegestoenhancesecurityandperformance:1)Createanewuserwithastrongpassword,2)Grantonlynecessarypermissionstothisuser,3)Regularlyreviewandupdateuserpermissions

MySQL String Data Types Explained: Choosing the Right Type for Your DataMay 11, 2025 am 12:10 AM

MySQLstringdatatypesshouldbechosenbasedondatacharacteristicsandusecases:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseBINARYorVARBINARYforbinarydatalikecryptographickeys.4)UseBLOBorTEXTforlargeuns

See all articles

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Roblox: Grow A Garden - Complete Mutation Guide

3 weeks agoByDDD

Roblox: Bubble Gum Simulator Infinity - How To Get And Use Royal Keys

3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

How to fix KB5055612 fails to install in Windows 10?

3 weeks agoByDDD

Nordhold: Fusion System, Explained

3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Mandragora: Whispers Of The Witch Tree - How To Unlock The Grappling Hook

3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SublimeText3 English version

Recommended: Win version, supports code prompts!

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.