首页新闻找找看学习计划

nhibernate+oracle分页问题

0
悬赏园豆:50 [已解决问题] 解决于 2012-11-30 14:11

问题描述:

create table t_test
(
p_id_vc varchar2(50),
rownum_n number
);

insert into T_TEST (P_ID_VC, ROWNUM_N)values ('1', 1);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('2', 2);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('3', 3);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('4', 4);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('5', 5);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('6', 6);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('7', 7);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('8', 8);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('9', 9);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('10', 10);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('11', 11);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('12', 12);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('sfs', 14);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('sdf', 15);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('sfs', 14);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('sdf', 15);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('sadfds32', 3);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('24', 4);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('sfsdf', 5);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('dsfsdf', 6);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('sdfsdf', 7);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('dsfsdf', 8);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('asda', 9);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('asdasdwe', 10);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('asdasda', 11);
insert into T_TEST (P_ID_VC, ROWNUM_N)values ('qweqw', 12);

根据nhibernate分页规则生成如下语句

select *  from (select row_.*, rownum rownum_          from (select p_id_vc, rownum_n from t_test) row_         where rownum <= 20 /* :p1 */        ) where rownum_ > 0   and p_id_vc in (select p_id_vc                     from (select row_.*, rownum rownum_                             from (select p_id_vc, rownum_n from t_test) row_                            where rownum <= 30 /* :p1 */                           )                    where rownum_ > 20)

查询结果为:

1 dsfsdf   6   20

也就是说在查询的前20条数据里面和第20到30条数据里面有一条数据是重复的(数据量大的时候,重复的个数越多)。

产生原因:

在oracle中,rownum每次插叙都是随机生成的。因此造成了在第二层进行<=比较的rownum的值与第一层进行>比较的值不一致。进而造成数据重复

解决方法:

请大婶们帮帮忙 想想解决方法

菜鸟老了的主页 菜鸟老了 | 初学一级 | 园豆:145
提问于:2012-11-26 09:28
< >
分享
最佳答案
0
SELECT * FROM ( SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A ) WHERE RN BETWEEN 21 AND 40
链接地址 http://www.cnblogs.com/Ronger/archive/2012/05/14/2498971.html
收获园豆:50
落幕残情 | 初学一级 |园豆:9 | 2012-11-26 17:09
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册