SELECT *
FROM (SELECT vtb.*, rownum rn
FROM (select cc.object_id cluster_id, --去掉这行可以查询到
cc.object_name cluster_name,
dd.vdc_id,
dd.vdc_name,
ff.vhost_purpose, --或去掉这行也可以查询到
bb.create_time,
ee.IP_ADDR,
aa.vhost_id,
bb.object_name vhost_name,
aa.fee
from (select vhost_id, sum(fee) fee
from tb_bil_fee_vhost
where 1 = 1
group by vhost_id) aa
inner join (select object_id,
object_name,
create_time,
object_type_id,
up_object_id
from tb_ast_object a
where (a.object_id, a.asset_find_id) in
(select object_id, max(asset_find_id)
from tb_ast_object b
group by object_id)) bb on aa.vhost_id =
bb.object_id
inner join (select object_id,
object_name,
create_time object_type_id
from tb_ast_object a
where (a.object_id, a.asset_find_id) in
(select object_id, max(asset_find_id)
from tb_ast_object b
group by object_id)) cc on bb.up_object_id =
cc.object_id
left join (select a.vhost_id, a.vdc_id, b.vdc_name
from tb_res_vdc_vhost a, tb_res_vdc b
where a.vdc_id = b.vdc_id) dd on aa.vhost_id =
dd.vhost_id
left join (select vhost_id, min(ip_addr) IP_ADDR
from tb_res_vhost_ip a
where (a.vhost_id, IP_ADDR, asset_find_id) in
(select vhost_id,
IP_ADDR,
max(asset_find_id)
from tb_res_vhost_ip
group by vhost_id, IP_ADDR)
group by vhost_id) ee on aa.vhost_id =
ee.vhost_id
left join tb_order_vhost ff on aa.vhost_id = ff.vhost_id
where 1 = 1
and cc.object_id = 27777 --24457--22568
order by cc.object_name, dd.vdc_name --或去掉这行也可以查询到
) vtb
WHERE rownum <= to_char(12)) lk
WHERE rn >= to_char(1)
加上rownum rn查询1条数据没有,去掉rownum rn则能够查询出数据,这是为什么呢?
求高手指点
FROM (SELECT vtb.*, rownum rn
FROM (select cc.object_id cluster_id, --去掉这行可以查询到
cc.object_name cluster_name,
dd.vdc_id,
dd.vdc_name,
ff.vhost_purpose, --或去掉这行也可以查询到
bb.create_time,
ee.IP_ADDR,
aa.vhost_id,
bb.object_name vhost_name,
aa.fee
from (select vhost_id, sum(fee) fee
from tb_bil_fee_vhost
where 1 = 1
group by vhost_id) aa
inner join (select object_id,
object_name,
create_time,
object_type_id,
up_object_id
from tb_ast_object a
where (a.object_id, a.asset_find_id) in
(select object_id, max(asset_find_id)
from tb_ast_object b
group by object_id)) bb on aa.vhost_id =
bb.object_id
inner join (select object_id,
object_name,
create_time object_type_id
from tb_ast_object a
where (a.object_id, a.asset_find_id) in
(select object_id, max(asset_find_id)
from tb_ast_object b
group by object_id)) cc on bb.up_object_id =
cc.object_id
left join (select a.vhost_id, a.vdc_id, b.vdc_name
from tb_res_vdc_vhost a, tb_res_vdc b
where a.vdc_id = b.vdc_id) dd on aa.vhost_id =
dd.vhost_id
left join (select vhost_id, min(ip_addr) IP_ADDR
from tb_res_vhost_ip a
where (a.vhost_id, IP_ADDR, asset_find_id) in
(select vhost_id,
IP_ADDR,
max(asset_find_id)
from tb_res_vhost_ip
group by vhost_id, IP_ADDR)
group by vhost_id) ee on aa.vhost_id =
ee.vhost_id
left join tb_order_vhost ff on aa.vhost_id = ff.vhost_id
where 1 = 1
and cc.object_id = 27777 --24457--22568
order by cc.object_name, dd.vdc_name --或去掉这行也可以查询到
) vtb
WHERE rownum <= to_char(12)) lk
WHERE rn >= to_char(1)
加上rownum rn查询1条数据没有,去掉rownum rn则能够查询出数据,这是为什么呢?
求高手指点
很简单,select * from t能够查询出数据,select t.*,rownum rn from t则查询不出数据
order by 不会过滤记录,内层查询中也没有rownum条件。
你首先要确定你的内层的语句是否能查询出记录。关于在于这一行
WHERE rownum <= to_char(12)) lk
首先to_char(12)是画蛇添足,其次rownum应该为rn,改为
WHERE rn <= 12) lk最后一行也把to_char去掉。
内层查询要是没有的话我就不来这发帖了,和where条件也没关系,去掉这行FROM (SELECT vtb.*, rownum rn
中的rownum rn就能查询出来即便是带有where条件,而加上的话就出不来,我把where条件去掉也是这个情况。
而且很不可思议的是有时候查的出来有时候查不来。
这个改成
rn<= 12
试试
我说了和where条件无关,我就是把where条件去掉,结果还是一样的。
FROM (SELECT vtb.*, rownum rn这行中rownum rn去掉的话就能查询出数据(不带where条件查询),加上的话有时候查询不出数据
--主要还是内层sql写的有问题,
/*
加rownum主要是为查询出来的数据指定一个行号,
如果有order by排序的话,是将带rownum的结果再次排序,
*/
SQL> select rownum rn,d.*
2 from dept d
3 order by rownum
4 /
RN DEPTNO DNAME LOC
---------- ------ -------------- -------------
1 10 ACCOUNTING NEW YORK
2 20 RESEARCH DALLAS
3 30 SALES CHICAGO
4 40 OPERATIONS BOSTON你应该了解select表达式的执行顺序
认识SQL的逻辑和物理执行顺序逻辑顺序
就是先把伪列加到结果集(内层sql)然后再按order by排序,即便是这样,也不会查不出来数据啊,排序会把数据排没了?我把where条件去掉了也是这样的。
有数据吗?
我给你个实例
select * from (select b.*,b.rownum hs from (select * from table a where 0=0 order by group by ) b) c where c.hs>0 and c.<hs<100
放外层就不能排序了,我就是想知道为什么这个sql这样写就出问题
看看表中数据的cc.object_name,dd.vdc_name是否有重复,可能是这个原因
当order by 和rownum一起使用时,order by 非主键 的时候,会出现顺序不一致的情况,光看你sql,看不出问题的,需要查看sql执行计划才能找到真正原因。
SELECT *
FROM (SELECT vtb.*, rownum rn
FROM (select cc.object_id cluster_id, --去掉这行可以查询到
cc.object_name cluster_name,
dd.vdc_id,
dd.vdc_name,
ff.vhost_purpose, --或去掉这行也可以查询到
bb.create_time,
ee.IP_ADDR,
aa.vhost_id,
bb.object_name vhost_name,
aa.fee
from (select vhost_id, sum(fee) fee
from tb_bil_fee_vhost
where 1 = 1
group by vhost_id) aa
inner join (select object_id,
object_name,
create_time,
object_type_id,
up_object_id
from tb_ast_object a
where (a.object_id, a.asset_find_id) in
(select object_id, max(asset_find_id)
from tb_ast_object b
group by object_id)) bb on aa.vhost_id =
bb.object_id
inner join (select object_id,
object_name,
create_time object_type_id
from tb_ast_object a
where (a.object_id, a.asset_find_id) in
(select object_id, max(asset_find_id)
from tb_ast_object b
group by object_id)) cc on bb.up_object_id =
cc.object_id
left join (select a.vhost_id, a.vdc_id, b.vdc_name
from tb_res_vdc_vhost a, tb_res_vdc b
where a.vdc_id = b.vdc_id) dd on aa.vhost_id =
dd.vhost_id
left join (select vhost_id, min(ip_addr) IP_ADDR
from tb_res_vhost_ip a
where (a.vhost_id, IP_ADDR, asset_find_id) in
(select vhost_id,
IP_ADDR,
max(asset_find_id)
from tb_res_vhost_ip
group by vhost_id, IP_ADDR)
group by vhost_id) ee on aa.vhost_id =
ee.vhost_id
left join tb_order_vhost ff on aa.vhost_id = ff.vhost_id
where 1 = 1
and cc.object_id = 27777 --24457--22568
order by cc.object_name, dd.vdc_name --或去掉这行也可以查询到
--问题就出在这里,你要明白一个事情,rownum 的编号规则,是先编号后排序
--当你编完号了,然后再排序那么查询出的数据就不一定是编的什么号了
--所以你在最外面查新 rn>=1 的时候是查不到的
--你可以把 rn>=1 去掉,看看结果中的rn是什么值都,肯定没有=1的数据。
) vtb
WHERE rownum <= to_char(12)) lk
WHERE rn >= to_char(1)
order by cc.object_name, dd.vdc_name --或去掉这行也可以查询到
--问题就出在这里,你要明白一个事情,rownum 的编号规则,是先编号后排序
--当你编完号了,然后再排序那么查询出的数据就不一定是编的什么号了
--所以你在最外面查新 rn>=1 的时候是查不到的
--你可以把 rn>=1 去掉,看看结果中的rn是什么值都,肯定没有=1的数据。
麻烦你仔细看下前面的我回的帖子,没有where rownum>和 where rn <的条件也是查不出来的
今天又遇到个问题select count(rownum) from (复杂的左连接 内连接查询) 报 ora-03113,通信信道结束
错误,以为是网络问题导致的,发现其他sql执行没问题,于是把这个sql在数据库服务器上直接执行,发现
还是报ora-03113,因此排除网络问题,最后找到资料说是oracle的一个bug,设计到rownum操作的全连接表查询会导致问题,不知道这个问题和ora-03113问题是不是一样因为oracle的bug引起的,因为两个sql的写法几乎一样
select * from
(select t.*,rownum rn from
(select ....
order by..)t
where rownum<=12) tt
where rn>=1; 是可以查到数据的
你可以看看这个地方对rownum的讲解
http://www.blogjava.net/wshsdlau/archive/2011/05/10/349932.html
1 的数值),否则无结果