create table tb as
select 'A1' name,'2010-06-21 00:12:26' vdtime, 15 cnt from dual union all
select 'A1' name,'2010-06-21 00:22:26' vdtime, 10 cnt from dual union all
select 'A2' name,'2010-06-21 01:22:26' vdtime, 15 cnt from dual union all
select 'A3' name,'2010-06-21 03:12:26' vdtime, 15 cnt from dual union all
select 'A4' name,'2010-06-21 07:52:26' vdtime, 15 cnt from dual union all
select 'A5' name,'2010-06-21 10:43:26' vdtime, 15 cnt from dual union all
select 'A6' name,'2010-06-21 15:20:26' vdtime, 15 cnt from dual;with t1 as
(
select to_char(to_date(max(vdtime),'yyyy-mm-dd hh24:mi:ss'),'hh24') mx,
to_char(to_date(min(vdtime),'yyyy-mm-dd hh24:mi:ss'),'hh24') mi
from tb
)
select substr(a.vdtime,1,10)||' '||lpad(to_number(b.mi)+rownum-1,2,0) times
from tb a,t1 b
--where substr(a.vdtime,12,2)>= b.mi and substr(a.vdtime,12,2)<= b.mx
connect by rownum <= b.mx -- mx = 15
为什么返回的是20条记录,而不是15条呢?明明b.mx=15
加上where substr(a.vdtime,12,2)>= b.mi and substr(a.vdtime,12,2)<= b.mx 过滤也是一样?
解决方案 »
- dts导出数据到oracle,怎么批量将表名小写字母改成大写?
- 问题
- oracle11g sql developer 的logging page那里显示个错误97怎样解决?
- oracle)PL/sql存储过程中直接使用insert...select语句,统计数据分别变0和1?
- oracle数据库通过dblink同步数据问题
- 请问如何查看ORACLE数据库执行的所有SQL语句?
- 请教一个sql脚本产生存储过程的问题
- 高分求助:oracle9i中如何完全删除表及表空间?
- oracle过程中的as和is有什么区别吗
- 有句SQL语句看不懂求帮忙解释下
- 询问一个表空间修改状态的问题
- Oracle与sqlserver数据类型对应
查询这个的时候就是7条查看原理
了解rownum背后的机制:
1 Oracle executes your query.2 Oracle fetches the first row and calls it row number 1.3 Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row.4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).5 Go to step 3.了解了原理,就知道rownum>不会成功,因为在第三步的时候查询出的行已经被丢弃,第四步查出来的rownum仍然是1,这样永远也不会成功。
我的理解是这句SQL先取出
select substr(a.vdtime,1,10)||' '||lpad(to_number(b.mi)+rownum-1,2,0) times
from tb a,t1 b
这部分的数据,共七条,然后用第一条进行递归(
connect by rownum <= b.mx ),结果应该是第一条数据重复15次,之后递归失效,但是第一次取出的七条数据中还有六条在,结果应该是15+6=21条。我这测试结果也是21条,不知道楼主是什么版本的ORACLE,能得到20条的结果。
楼主可以一试,加上Start with rownum=1 结果还是21条,如果改成Start with rownum<3 结果同样是21条。
------------------------- ----------
2010-06-21 00 1
2010-06-21 01 2
2010-06-21 02 3
2010-06-21 03 4
2010-06-21 04 5
2010-06-21 05 6
2010-06-21 06 7
2010-06-21 07 8
2010-06-21 08 9
2010-06-21 09 10
2010-06-21 10 11
2010-06-21 11 12
2010-06-21 12 13
2010-06-21 13 14
2010-06-21 14 15
2010-06-21 15 1
2010-06-21 16 1
2010-06-21 17 1
2010-06-21 18 1
2010-06-21 19 1
2010-06-21 20 1
可以看到只有第一行,即rownum=1等时候是完整的完成了递归;其它行数据当level=2的时候则不满足CONNECT BY条件,递归终止。
SQL> with t1 as
2 (
3 select to_char(to_date(max(vdtime),'yyyy-mm-dd hh24:mi:ss'),'hh24') mx,
4 to_char(to_date(min(vdtime),'yyyy-mm-dd hh24:mi:ss'),'hh24') mi
5 from tb
6 )
7 select level,rownum,substr(a.vdtime,1,10)||' '||lpad(to_number(b.mi)+rownum-1,2,0) times
8 from tb a,t1 b
9 --where substr(a.vdtime,12,2)>= b.mi and substr(a.vdtime,12,2)<= b.mx
10 connect by rownum <= b.mx -- mx = 15
11 ;
LEVEL ROWNUM TIMES
---------- ---------- -------------------------
1 1 2010-06-21 00
2 2 2010-06-21 01
3 3 2010-06-21 02
4 4 2010-06-21 03
5 5 2010-06-21 04
6 6 2010-06-21 05
7 7 2010-06-21 06
8 8 2010-06-21 07
9 9 2010-06-21 08
10 10 2010-06-21 09
11 11 2010-06-21 10
12 12 2010-06-21 11
13 13 2010-06-21 12
14 14 2010-06-21 13
15 15 2010-06-21 14
1 16 2010-06-21 15 --这里虽然level为1,可是rownum已经是16了,不满足connect by的<=15条件了,因此不会继续进行递归查询。所以后面的数据都只列出了LEVEL为1的记录
1 17 2010-06-21 16
1 18 2010-06-21 17
1 19 2010-06-21 18
1 20 2010-06-21 19
LEVEL ROWNUM TIMES
---------- ---------- -------------------------
1 21 2010-06-21 20
21 rows selected
SQL>
看看结果就不一样了
(
select to_char(to_date(max(vdtime),'yyyy-mm-dd hh24:mi:ss'),'hh24') mx,
to_char(to_date(min(vdtime),'yyyy-mm-dd hh24:mi:ss'),'hh24') mi
from tb
)
select substr(a.vdtime,1,10)||' '||lpad(to_number(b.mi)+rownum,2,0) times
from tb a,t1 b
where level>1
connect by rownum <= b.mx
(
select to_char(to_date(max(vdtime),'yyyy-mm-dd hh24:mi:ss'),'hh24') mx,
to_char(to_date(min(vdtime),'yyyy-mm-dd hh24:mi:ss'),'hh24') mi
from tb
)select a.name,substr(a.vdtime,1,10)||' '||lpad(to_number(b.mi)+rownum-1,2,0) times
from tb a,t1 b
where level>1
connect by rownum-1 <= b.mx