子查询中CONNECT BY的工作方式 [英] How CONNECT BY in subquery works

问题描述

我有一个如下查询

select substr( 'ORACLE DEVELOPER',level,1 ) ITEM
from dual connect by level <= length('ORACLE DEVELOPER') ;

它返回"O"作为结果.

it returns 'O' as the result.

但是如果我将其作为其他子查询，它将显示所需的输出. 查询如下.

but if i put this as a subquery in other it is displaying the required output. query is as below.

select a.ITEM from (select substr( 'ORACLE DEVELOPER',level,1 ) ITEM
from dual connect by level <= length('ORACLE DEVELOPER') ) a

此CONNECT BY如何在子查询中工作. 我是这个功能的新手.谁能解释这个??

How this CONNECT BY is working in subquery. I am new to this feature. Can anyone explain this??

推荐答案

CONNECT BY是用于执行分层查询的Oracle SQL语法. 此处.

CONNECT BY is Oracle SQL syntax for doing hierarchical queries. Some good examples are here.

副作用之一是它允许您多次查询单个数据源，这意味着有一个方便的技巧可以使用以下方法生成任意数量的行:

One of the side effects is that it allows you to query a single data source multiple times, which means that there's a handy trick to generate any number of rows with:

SELECT 1
FROM dual
CONNECT BY LEVEL <= 10;

以上将双对偶连接到自身10次-LEVEL伪列来自层次结构；因为CONNECT BY子句实际上并未引用行源中的任何数据，所以它有效地使自己的子代，孙子代，曾孙子代为双数，直到CONNECT BY子句的计算结果为false为止(在这种情况下，当LEVEL变大大于10).

The above will connect dual back to itself 10 times - the LEVEL pseudocolumn is from the hierarchy; because the CONNECT BY clause doesn't actually refer to any data from the rowsource, it effectively makes dual its own child, grandchild, great-grandchild, etc. until the CONNECT BY clause evaluates to false (in this case, when LEVEL becomes greater than 10).

在您的情况下，您正在为字符串的每个字母生成一行(而不是10，而是指LENGTH('a string')，这是一种获取查询以针对每个字符串返回一条记录的好方法字符串中的字母.然后使用SUBSTR从字符串中选择第n个字母.

In your case, you are generating a row for each letter of the string (instead of 10, you are referring to LENGTH('a string'), which is a nice way of getting a query to return one record for each letter in the string. You are then using SUBSTR to pick out the nth letter from the string.

这篇关于子查询中CONNECT BY的工作方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！