为什么下面两个分页语句查询出来的记录都会有msisdn = 13407711511的记录;
其中test_a,dim_home_area 表都没有重复的数据
select * from (
select *
from (select row_.*, rownum rownum_
from (select a.msisdn,a.name,a.home_county
FROM test_a a
WHERE a.home_county in (select home_area_code from dim_home_area where (home_area_group_code = '771'))
order by a.home_county) row_
where rownum < 120001
)
where rownum_ >= 60001
) where msisdn in (13407711511) ;
select * from (
select *
from (select row_.*, rownum rownum_
from (select a.msisdn,a.name,a.home_county
FROM test_a a
WHERE a.home_county in (select home_area_code from dim_home_area where (home_area_group_code = '771'))
order by a.home_county) row_
where rownum < 60001
)
where rownum_ >= 1
) where msisdn in (13407711511) ;
其中test_a,dim_home_area 表都没有重复的数据
select * from (
select *
from (select row_.*, rownum rownum_
from (select a.msisdn,a.name,a.home_county
FROM test_a a
WHERE a.home_county in (select home_area_code from dim_home_area where (home_area_group_code = '771'))
order by a.home_county) row_
where rownum < 120001
)
where rownum_ >= 60001
) where msisdn in (13407711511) ;
select * from (
select *
from (select row_.*, rownum rownum_
from (select a.msisdn,a.name,a.home_county
FROM test_a a
WHERE a.home_county in (select home_area_code from dim_home_area where (home_area_group_code = '771'))
order by a.home_county) row_
where rownum < 60001
)
where rownum_ >= 1
) where msisdn in (13407711511) ;
select * from (
select *
from (select row_.*, rownum rownum_
from (select a.msisdn,a.name,a.home_county
FROM test_a a
WHERE a.home_county in (select home_area_code from dim_home_area where (home_area_group_code = '771'))
order by a.home_county) row_
)
where rownum_ >= 60001 and rownum_ < 120001
) where msisdn in (13407711511) ;
select * from (
select *
from (select row_.*, rownum rownum_
from (select a.msisdn,a.name,a.home_county
FROM test_a a
WHERE a.home_county in (select home_area_code from dim_home_area where (home_area_group_code = '771'))
order by a.home_county) row_
)
where rownum_ >= 1 and rownum_ < 60001
) where msisdn in (13407711511) ;
按理排序出来的序号应该是一致的,还是这样写分页语句有bug,不过以前在网上看到的分页语句大都是这样写的。
(select a.msisdn,a.name,a.home_country
FROM test_a a
WHERE a.home_county in (select home_area_code from dim_home_area where (home_area_group_code = '771'))
order by a.home_county) row_
where rownum < 120001
--你这个sql的效果等同于
select a.msisdn,a.name,a.home_country
FROM test_a a
WHERE a.home_county in (select home_area_code from dim_home_area where (home_area_group_code = '771')) and rownum < 120001 order by a.home_county
select *
from (select rownum rownnum_,a.msisdn,a.name,a.home_county
FROM test_a a
WHERE a.home_county in (select home_area_code from dim_home_area where (home_area_group_code = '771'))
order by a.home_county
)
where rownum_ >= 60001 and rownum_ < 120001; select *
from (select rownum rownum_,a.msisdn,a.name,a.home_county
FROM test_a a
WHERE a.home_county in (select home_area_code from dim_home_area where (home_area_group_code = '771'))
order by a.home_county
)
where rownum_ >= 1 and rownum_ < 60001;
select rownum rownnum_,a.msisdn,a.name,a.home_county
FROM test_a a
WHERE a.home_county in (select home_area_code from dim_home_area where (home_area_group_code = '771'))
order by a.home_county
这样的sql不对吧,如果我把where (home_area_group_code = '771')这个条件去掉即下面的代码,select rownum rownnum_,a.msisdn,a.name,a.home_county
FROM test_a a
WHERE a.home_county in (select home_area_code from dim_home_area )
order by a.home_county 结果就不是你想要排序了。
FROM test_a a
WHERE a.home_county in (select home_area_code from dim_home_area where (home_area_group_code = '771'))
order by a.home_county这个不是先排序完后,在嵌套一层取它的rownum值吗?
另外,9楼的sql是不对的,我在9楼是想告诉你,你写的那个为什么不对