oracle rownum和connect by 扫盲
来源:互联网 发布:办公审批软件 编辑:程序博客网 时间:2024/06/07 00:56
最近需要实现一个功能:有这样一个数据
count user_name
2 周
3 吴
实现结果:
user_name
周
周
吴
吴
吴
在网上找到了这样的方法:
with t as(
select 2 as count, '周' user_name from dual
union all
select 3,'吴' from dual
)
select * from t,
(select rownum rn from t connect by rownum <= 3) b
where count>=rn
非常有趣,就去了解了rownum和connect by
ROWNUM是对结果集加的一个伪列,即先查到结果集之后再加上去的一个列 (强调:先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1,而有其他大于1的值。
ROWNUM是一个序列,是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1,第二条为2,依次类 推。如果你用>,>=,=,between...and这些条件,因为从缓冲区或数据文件中得到的第一条记录的rownum为1,则被删除, 接着取下条,可是它的rownum还是1,又被删除,依次类推,便没有了数据。
rowid 与 rownum 虽都被称为伪列,但它们的存在方式是不一样的,rowid 可以说是物理存在的,表示记录在表空间中的唯一位置ID,在DB中唯一。只要记录没被搬动过,rowid是不变的。rowid 相对于表来说又像表中的一般列,所以以 rowid 为条件就不会有 rownum那些情况发生。
另外还要注意:rownum不能以任何基表的名称作为前缀。
with x as ( select 'aa' chr,1 pid,4 id from dual union all select 'bb' chr ,2,5 from dual/* union all select 'cc' chr ,3,6 from dual*/) select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other from x /* start with pid= 1 */ connect by level<=3
第一行和第八行同属第一层级,第二到第四是第二层级,第三是第三层,看下面的图
如果初始行数是n,总层级是m,这棵树总行数函数是:
f(n,1)=n,
f(n,m)=n*f(n,m-1)+n;
总记录数N,level层数M
结果集数:T=∑N^x(x=1...m)
比如,总记录数为3,层数为3
则结果集数:3^1 +3^2 + 3^3 = 3+9+27=39
接下来是细节:
SELECT [LEVEL] ,column,expression,...
FROM table_name
[WHERE where_clause]
[[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
[[START WITH start_condition] 讲的是这棵树从哪里开始;prior_condition 具体这样:
prior 列名1=列名2
意思是之前一行的列名1=这一行的列名2
也可以这么写 列名1=prior列名2 ;
还有sys_connect_by_path的用法:
sys_connect_by_path( 列名,‘分隔符号’ )
结合例子就明白了:
with x as ( select 'aa' chr,1 pid,4 id from dual union all select 'bb' chr ,2,1 from dual)/* select level ,sys_connect_by_path(pid, '/' ) from x start with pid= 1 connect by prior pid = id ;*/ select level ,ltrim(sys_connect_by_path( id , '/' ),'/') from x start with pid= 1 connect by prior pid = id ;
好像看官应该不太容易明白。。。
自己练习吧。
又碰到这样一种情况
with t as(select 2 as count, '周' user_name from dualunion allselect 3,'吴' from dual/*union allselect 4,'郑' from dual*\*/)select rownum rn,t.user_name,level from t connect by rownum<=10结果:
我就去找connect by rownum 和level的区别
重点是rownum,connect by rownum,没有其他条件,是直接递归迭代第一行(反复),然后判断rownum条件,不满足条件结束。正是因为
--递归如果没有start with则每行都是根,先第一轮递归,递归到rownum<n不满足后结束,然后其他根肯定不满足,只选出自身一行。
--则剩余其他几行都显示一次,因为第1行递归结束后,继续其他行,发现都不满足,结束。这个rownum不是select里的rownum
--connect by 1=1就不结束了
--没有start with,没有prior...的迭代,不管是level,还是rownum,每行都是根节点,自身或其它节点是子节点
--区别是connect by level会先深度搜索,也就是根---自身---其他节点
--connect by rownum则是不停迭代自身(第一行),然后判断rownum。。。
真相大白。
又折腾出一种情况:
with t as(select 2 as count, '周' user_name from dualunion allselect 3,'吴' from dualunion allselect 4,'郑' from dual)select rownum rn,t.user_name,level from t connect by rownum=3
当rownum=固定值的情况,可以测试不同值,负数、0,1,2,等,结果不同;保底3个,看能否符合rownum情况决定是否会多一行和该位置;
还有加where条件,connect by加多个条件==。
http://www.cnblogs.com/szlbm/p/5806070.html
http://blog.csdn.net/haiross/article/details/17586565
http://blog.csdn.net/leshami/article/details/5616877
http://www.itpub.net/thread-1615513-1-1.html
http://www.itpub.net/forum.php?mod=viewthread&tid=1570306
- oracle rownum和connect by 扫盲
- Oracle数据库的CONNECT BY ROWNUM研究
- connect by rownum造表
- oracle 分页 rownum 和 order by
- oracle order by和rownum 一起用
- level、connect by、rownum使用
- rownum和order by
- 利用connect by rownum构造日期列表
- Oracle 中的树查询和 connect by
- oracle 的 CONNECT BY 和START WITH
- Oracle递归 Start with 和Connect By
- Oracle 中的树查询和 connect by
- ORACLE中rownum和order by 的关系
- ORACLE数据库UNION集合ORDER BY 和 ROWNUM的使用
- ORACLE数据库中的ROWNUM和ORDER BY执行顺序
- Oracle数据库中的ROWNUM和ORDER BY执行顺序
- Oracle数据库中的ROWNUM和ORDER BY执行顺序
- oracle rownum order by 分页
- 前端学习
- java基础—二(基础语法)
- 刷题随手记录 知识点c++
- Yii2.0 behaviors方法使用
- C/C++ static的作用
- oracle rownum和connect by 扫盲
- MongoDB的shell操作
- 分解滤波组和综合滤波组
- 利用electron-packager打包exe程序
- 【BZOJ 1055】【HAOI 2008】玩具取名【区间动规】
- 笔记本无线网黄色感叹号
- 解决win10以太网没有有效的ip配置
- Unix下C程序内存泄漏检测工具Valgrind安装与使用
- Linux下安装Eclipse及其插件