Oracle10g中层次查询简介
我们可以通过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'); Copy after login |
那么我们可以使用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 ShoresCopy after login |
自从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 Copy after login |
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>---------------------------------- Copy after login 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 Copy after login |
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
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
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
12306 How to check historical ticket purchase records How to check historical ticket purchase records
Mar 28, 2024 pm 03:11 PM
Download the latest version of 12306 ticket booking app. It is a travel ticket purchasing software that everyone is very satisfied with. It is very convenient to go wherever you want. There are many ticket sources provided in the software. You only need to pass real-name authentication to purchase tickets online. All users You can easily buy travel tickets and air tickets and enjoy different discounts. You can also start booking reservations in advance to grab tickets. You can book hotels or special car transfers. With it, you can go where you want to go and buy tickets with one click. Traveling is simpler and more convenient, making everyone's travel experience more comfortable. Now the editor details it online Provides 12306 users with a way to view historical ticket purchase records. 1. Open Railway 12306, click My in the lower right corner, and click My Order 2. Click Paid on the order page. 3. On the paid page
How to check your academic qualifications on Xuexin.com
Mar 28, 2024 pm 04:31 PM
How to check my academic qualifications on Xuexin.com? You can check your academic qualifications on Xuexin.com, but many users don’t know how to check their academic qualifications on Xuexin.com. Next, the editor brings you a graphic tutorial on how to check your academic qualifications on Xuexin.com. Interested users come and take a look! Xuexin.com usage tutorial: How to check your academic qualifications on Xuexin.com 1. Xuexin.com entrance: https://www.chsi.com.cn/ 2. Website query: Step 1: Click on the Xuexin.com address above to enter the homepage Click [Education Query]; Step 2: On the latest webpage, click [Query] as shown by the arrow in the figure below; Step 3: Then click [Login Academic Credit File] on the new page; Step 4: On the login page Enter the information and click [Login];
Python ORM Performance Benchmark: Comparing Different ORM Frameworks
Mar 18, 2024 am 09:10 AM
Object-relational mapping (ORM) frameworks play a vital role in python development, they simplify data access and management by building a bridge between object and relational databases. In order to evaluate the performance of different ORM frameworks, this article will benchmark against the following popular frameworks: sqlAlchemyPeeweeDjangoORMPonyORMTortoiseORM Test Method The benchmarking uses a SQLite database containing 1 million records. The test performed the following operations on the database: Insert: Insert 10,000 new records into the table Read: Read all records in the table Update: Update a single field for all records in the table Delete: Delete all records in the table Each operation
How to check the activation date on Apple mobile phone
Mar 08, 2024 pm 04:07 PM
If you want to check the activation date using an Apple mobile phone, the best way is to check it through the serial number in the mobile phone. You can also check it by visiting Apple's official website, connecting it to a computer, and downloading third-party software to check it. How to check the activation date of Apple mobile phone Answer: Serial number query, Apple official website query, computer query, third-party software query 1. The best way for users is to know the serial number of their mobile phone. You can see the serial number by opening Settings, General, About This Machine. . 2. Using the serial number, you can not only know the activation date of your mobile phone, but also check the mobile phone version, mobile phone origin, mobile phone factory date, etc. 3. Users visit Apple's official website to find technical support, find the service and repair column at the bottom of the page, and check the iPhone activation information there. 4. User
Comparison of similarities and differences between MySQL and PL/SQL
Mar 16, 2024 am 11:15 AM
MySQL and PL/SQL are two different database management systems, representing the characteristics of relational databases and procedural languages respectively. This article will compare the similarities and differences between MySQL and PL/SQL, with specific code examples to illustrate. MySQL is a popular relational database management system that uses Structured Query Language (SQL) to manage and operate databases. PL/SQL is a procedural language unique to Oracle database and is used to write database objects such as stored procedures, triggers and functions. same
Application of Python ORM in big data projects
Mar 18, 2024 am 09:19 AM
Object-relational mapping (ORM) is a programming technology that allows developers to use object programming languages to manipulate databases without writing SQL queries directly. ORM tools in python (such as SQLAlchemy, Peewee, and DjangoORM) simplify database interaction for big data projects. Advantages Code Simplicity: ORM eliminates the need to write lengthy SQL queries, which improves code simplicity and readability. Data abstraction: ORM provides an abstraction layer that isolates application code from database implementation details, improving flexibility. Performance optimization: ORMs often use caching and batch operations to optimize database queries, thereby improving performance. Portability: ORM allows developers to
Discuz database location query skills sharing
Mar 10, 2024 pm 01:36 PM
Forum is one of the most common website forms on the Internet. It provides users with a platform to share information, exchange and discuss. Discuz is a commonly used forum program, and I believe many webmasters are already very familiar with it. During the development and management of the Discuz forum, it is often necessary to query the data in the database for analysis or processing. In this article, we will share some tips for querying the location of the Discuz database and provide specific code examples. First, we need to understand the database structure of Discuz
How to check the latest price of Tongshen Coin?
Mar 21, 2024 pm 02:46 PM
How to check the latest price of Tongshen Coin? Token is a digital currency that can be used to purchase in-game items, services, and assets. It is decentralized, meaning it is not controlled by governments or financial institutions. Transactions of Tongshen Coin are conducted on the blockchain, which is a distributed ledger that records the information of all Tongshen Coin transactions. To check the latest price of Token, you can use the following steps: Choose a reliable price check website or app. Some commonly used price query websites include: CoinMarketCap: https://coinmarketcap.com/Coindesk: https://www.coindesk.com/ Binance: https://www.bin
