为什么表上的CONNECT BY LEVEL返回多余的行? [英] Why does CONNECT BY LEVEL on a table return extra rows?

问题描述

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

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

假设下表:

create table a ( id number );

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

此查询返回12行( SQL提琴).

This query returns 12 rows (SQL Fiddle).

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

表A中每行一行，列LVL的值为1，表A中每行三列的值LVL为2，即:

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.:

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

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

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.

将已连接"的级别数增加到3，每个返回13行 ID. LVL为1时为1，LVL为2时为3，LVL为3时为9，这似乎表明返回的行是表A中的行数，乘以LVL值减1的幂.

Increasing the number of levels that are "connected" to 3 returns 13 rows for 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.

尽管如此，我希望这些查询与以下查询相同，该查询返回 6行

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

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

对于我来说，文档并不是很清楚应该发生什么.这些权力发生了什么?为什么前两个查询与第三个查询不同?

The documentation isn'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?

推荐答案

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

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.

对于3条记录:

• 级别1:3条记录(均具有1级记录)
• Lvl 2:3条记录的级别为1 + 3 * 3条记录的级别为2 = 12
• Lvl 3:3 + 3 * 3 + 3 * 3 * 3 = 39(实际上，每个记录13条).
• 第四级:开始看到规律了吗? :)

这并不是真正的交叉联接.交叉联接将仅返回此查询结果中具有级别2的记录，而通过此连接，您将获得具有级别1的记录以及具有级别2的记录，因此结果为3 + 3 * 3，而不仅仅是3 * 3记录.

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.

这篇关于为什么表上的CONNECT BY LEVEL返回多余的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！