SQL 为什么表上的 CONNECT BY LEVEL 返回额外的行？

Why does CONNECT BY LEVEL on a table return extra rows?

提问by Ben

Using CONNECT BY LEVEL seems to return too many rows when performed on a table. What is the logic behind what's happening?

在表上执行时，使用 CONNECT BY LEVEL 似乎返回太多行。发生的事情背后的逻辑是什么？

Assuming the following table:

假设下表：

create table a ( id number );

insert into a values (1);
insert into a values (2);
insert into a values (3);

This query returns 12 rows (SQL Fiddle).

此查询返回 12 行 ( SQL Fiddle)。

 select id, level as lvl
   from a
connect by level <= 2
  order by id, level

One row for each in table A with the value of column LVL being 1 and three for each in table A where the column LVL is 2, i.e.:

表 A 中的每一行，其中列 LVL 的值为 1；表 A 中的每一行，其中列 LVL 的值为 2，即：

ID | LVL 
---+-----
 1 |  1 
 1 |  2 
 1 |  2 
 1 |  2 
 2 |  1 
 2 |  2 
 2 |  2 
 2 |  2 
 3 |  1 
 3 |  2 
 3 |  2 
 3 |  2 

It is equivalent to this query, which returns the same results.

它相当于这个查询，它返回相同的结果。

 select id, level as lvl
   from dual
  cross join a
connect by level <= 2
  order by id, level

I don't understand why these queries return 12 rows or why there are three rows where LVL is 2 and only one where LVL is 1 for each value of the ID column.

我不明白为什么这些查询返回 12 行，或者为什么对于 ID 列的每个值，有三行 LVL 为 2，而只有一行 LVL 为 1。

Increasing the number of levels that are "connected" to 3 returns 13 rowsfor each value of ID. 1 where LVL is 1, 3 where LVL is 2 and 9 where LVL is 3. This seems to suggest that the rows returned are the number of rows in table A to the power of the value of LVL minus 1.

将“连接”的级别数增加到 3 会为每个 ID 值返回 13 行。1，其中 LVL 为 1，3，其中 LVL 为 2，9，其中 LVL 为 3。这似乎表明返回的行数是表 A 中行数的 LVL 值减去 1 的幂。

I would have though that these queries would be the same as the following, which returns 6 rows

我认为这些查询将与以下相同，它返回 6 行

select id, lvl
  from ( select level  as lvl
           from dual
        connect by level  <= 2
                )
 cross join a
 order by id, lvl

The documentationisn't particularly clear, to me, in explaining what should occur. What's happening with these powers and why aren't the first two queries the same as the third?

该文件还不是特别清楚，对我来说，在解释应该发生什么。这些权力发生了什么，为什么前两个查询与第三个查询不同？

采纳答案by GolezTrol

In the first query, you connect by just the level. So if level <= 1, you get each of the records 1 time. If level <= 2, then you get each level 1 time (for level 1) + N times (where N is the number of records in the table). It is like you are cross joining, because you're just picking all records from the table until the level is reached, without having other conditions to limit the result. For level <= 3, this is done again for each of those results.

在第一个查询中，您仅按级别进行连接。因此，如果级别 <= 1，您将获得每条记录 1 次。如果级别 <= 2，那么您将获得每个级别 1 次（对于级别 1）+ N 次（其中 N 是表中的记录数）。这就像您在交叉连接，因为您只是从表中挑选所有记录直到达到级别，而没有其他条件来限制结果。对于级别 <= 3，对每个结果再次执行此操作。

So for 3 records:

所以对于 3 条记录：

• Lvl 1: 3 record (all having level 1)
• Lvl 2: 3 records having level 1 + 3*3 records having level 2 = 12
• Lvl 3: 3 + 3*3 + 3*3*3 = 39 (indeed, 13 records each).
• Lvl 4: starting to see a pattern? :)

• Lvl 1: 3 记录（全部为 1 级）
• Lvl 2：3 条记录为 1 + 3*3 条记录为 2 = 12
• Lvl 3：3 + 3*3 + 3*3*3 = 39（确实，每条记录 13 条）。
• Lvl 4：开始看到一个模式？:)

It's not really a cross join. A cross join would only return those records that have level 2 in this query result, while with this connect by, you get the records having level 1 as well as the records having level 2, thus resulting in 3 + 3*3 instead of just 3*3 record.

这不是真正的交叉连接。交叉联接将只返回在此查询结果中具有级别 2 的那些记录，而通过此连接，您将获得具有级别 1 的记录以及具有级别 2 的记录，从而导致 3 + 3*3 而不仅仅是3*3 记录。

回答by Nick Krasnov

When connect byis used without start withclause and prioroperator, there is no restriction on joining children row to a parent row. And what Oracle does in this situation, it returns all possible hierarchy permutations by connecting a row to every row of level higher.

当connect by不带start with子句和prior运算符使用时，将子行连接到父行没有限制。在这种情况下，Oracle 所做的就是通过将一行连接到更高级别的每一行来返回所有可能的层次结构排列。

SQL> select b
  2       , level as lvl
  3       , sys_connect_by_path(b, '->') as ph
  4     from a
  5  connect by level <= 2
  6  ;

         B        LVL PH
       ---------- ---------- 
         1          1 ->1
         1          2 ->1->1
         2          2 ->1->2
         3          2 ->1->3
         2          1 ->2
         1          2 ->2->1
         2          2 ->2->2
         3          2 ->2->3
         3          1 ->3
         1          2 ->3->1
         2          2 ->3->2
         3          2 ->3->3

12 rows selected

回答by DazzaL

you're comparing apples to oranges when comparing the final query to the others as the LEVEL is isolated in that to the 1-row dual table.

当将最终查询与其他查询进行比较时，您将苹果与橙子进行比较，因为 LEVEL 与 1 行双表隔离。

lets consider this query:

让我们考虑这个查询：

 select id, level as lvl
   from a
connect by level <= 2
  order by id, level

what that is saying is, start with the table set (select * From a). then, for each row returned connect this row to the prior row. as you have not defined a join in the connect by, this is in effect a Cartesian join, so when you have 3 rows of (1,2,3) 1 joins to 2, 1->3, 2->1, 2->3, 3->1 and 3->2 and they also join to themselves 1->1,2->2 and 3->3. these joins are level=2. so we have 9 joins there, which is why you get 12 rows (3 original "level 1" rows plus the Cartesian set).

这就是说，从表集开始（select * From a）。然后，对于返回的每一行，将此行连接到前一行。由于您尚未在连接中定义连接，这实际上是笛卡尔连接，因此当您有 3 行 (1,2,3) 1 连接到 2, 1->3, 2->1, 2 ->3, 3->1 和 3->2 并且它们也连接到它们自己 1->1,2->2 和 3->3。这些连接是 level=2。所以我们在那里有 9 个连接，这就是为什么你得到 12 行（3 个原始的“级别 1”行加上笛卡尔集）。

so the number of rows output = rowcount + (rowcount^2)

所以输出的行数 = rowcount + (rowcount^2)

in the last query you are isolating level to this

在最后一个查询中，您将级别与此隔离

select level  as lvl
           from dual
        connect by level  <= 2

which of course returns 2 rows. this is then cartesianed to the original 3 rows, giving 6 rows as output.

这当然返回 2 行。然后将其笛卡尔化为原始 3 行，给出 6 行作为输出。

回答by AlexiWilius

You can use technique below to overcome this issue:

您可以使用以下技术来解决此问题：

select id, level as lvl
   from a
      left outer join (select level l from dual connect by level <= 2) lev on 1 = 1
order by id