一个SQL问题（寻求解答中）

dingjun123

原帖由 luckysea 于 2011-2-24 11:44 发表
总感觉开发比较简单，其实不然。看来需要重视了，呵呵

开发要简单的话，那DBA会减少一大批，我认为没有啥东西是简单的，立场和角度不同，比如有的DBA就专门干备份恢复，脚本现成的，什么都有脚本和规则，他认为DBA简单，但是很多人认为DBA不简单

newkid

比较有意思的另类写法：

create table test (id number, cnt number,c1 varchar2(10),c2 varchar2(10));

insert into test values (1,3,'aa', 'aaa');
insert into test values (2,4,'bb', 'bbb');   

SELECT * FROM test
CONNECT BY id=PRIOR id AND LEVEL<=cnt AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;


SELECT test.*
  FROM test
      ,TABLE( CAST( MULTISET( SELECT LEVEL
                                FROM DUAL
                              CONNECT BY LEVEL <= cnt
                            )
              AS SYS.ODCINUMBERLIST )  
            ) t2
;

regonly1

动态构建每个组的虚拟行数，呵呵，学习了

alafqq

你好，

SELECT *
  FROM test4
CONNECT BY cnt = PRIOR cnt
       AND LEVEL <= cnt
       AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

对于这个写法不是很理解，可以剖析，或者分解下么？嘻嘻

newkid

你能理解CONNECT BY吧？它相当于是一个递归的自连接，不断地把每层的连接结果叠加到结果集中。两层之间的连接条件和递归出口写在CONNECT BY中。在这里我们的数据并无父子关系，只是要让同一行数据重复出现，因此我们的连接的条件只用到了表的主键id=PRIOR id, 此外再用LEVEL控制层数作为递归出口。但ORACLE有个检查，如果你有前后连接条件(id=PRIOR id)，但是同一行数据再次出现，它就会报一个错：
ERROR:
ORA-01436: CONNECT BY loop in user data
为了欺骗它，这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果，所以它认为两行数据不一样，所以不报错了。

alafqq

说实话，connect by 还真不知怎么理解。 connect by [ prior ] [start with] 我知道是在一起。
单只有connect by ，如下句
select t.*,level from dual t connect by level<10
怎么理解呢？递归的自连接，那么，这里的连接 只的是 * 连上 *
假设我有个One表， 他有 id,name 2列。
create table ONE
(
  ID   VARCHAR2(20),
  NAME VARCHAR2(20)
);

insert into one(id,name) values (9,96);
insert into one(id,name) values (123,22);

select id,name,level from one connect by level<3
那么在上面这句话中，这里的连接，是指什么连接呢？id单独连id？name单独连name?抑或是2者都关联？

kmpx

这个也可以从父子的角度去看
对于ORA-01436，也是指同一行记录即是父又是子，这是不允许的

newkid

原帖由 alafqq 于 2011-3-3 14:36 发表
说实话，connect by 还真不知怎么理解。 connect by [ prior ] [start with] 我知道是在一起。
单只有connect by ，如下句
select t.*,level from dual t connect by level<10
怎么理解呢？递归的自连接，那么，这里的连接 只的是 * 连上 *
假设我有个One表， 他有 id,name 2列。
create table ONE
(
  ID   VARCHAR2(20),
  NAME VARCHAR2(20)
);

insert into one(id,name) values (9,96);
insert into one(id,name) values (123,22);

select id,name,level from one connect by level<3
那么在上面这句话中，这里的连接，是指什么连接呢？id单独连id？name单独连name?抑或是2者都关联？

你这个语句根本没有前后级的连接条件，只有中止循环的条件LEVEL<3, 所以它相当于每次都把当前层的数据和ONE表作一个笛卡尔连接。
奇怪的是ORACLE在这种情况下不报错，即使同一行数据不断出现。但如果有连接条件，一行数据重复出现它就报错。

jboracle1981

原帖由 newkid 于 2011-3-2 23:29 发表
你能理解CONNECT BY吧？它相当于是一个递归的自连接，不断地把每层的连接结果叠加到结果集中。两层之间的连接条件和递归出口写在CONNECT BY中。在这里我们的数据并无父子关系，只是要让同一行数据重复出现，因此我们的连接的条件只用到了表的主键id=PRIOR id, 此外再用LEVEL控制层数作为递归出口。但ORACLE有个检查，如果你有前后连接条件(id=PRIOR id)，但是同一行数据再次出现，它就会报一个错：
ERROR:
ORA-01436: CONNECT BY loop in user data
为了欺骗它，这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果，所以它认为两行数据不一样，所以不报错了。

学习了

6666444

原帖由 newkid 于 2011-3-2 23:29 发表
你能理解CONNECT BY吧？它相当于是一个递归的自连接，不断地把每层的连接结果叠加到结果集中。两层之间的连接条件和递归出口写在CONNECT BY中。在这里我们的数据并无父子关系，只是要让同一行数据重复出现，因此我们的连接的条件只用到了表的主键id=PRIOR id, 此外再用LEVEL控制层数作为递归出口。但ORACLE有个检查，如果你有前后连接条件(id=PRIOR id)，但是同一行数据再次出现，它就会报一个错：
ERROR:
ORA-01436: CONNECT BY loop in user data
为了欺骗它，这里用了一个PRIOR DBMS_RANDOM.VALUE, 因为DBMS_RANDOM.VALUE每次调用都返回不同结果，所以它认为两行数据不一样，所以不报错了。

PRIOR DBMS_RANDOM.VALUE学习．．．今天在另外一帖看到了这个，特来学习