After finishing the project yesterday to test a test, said that the paging query does not seem to work, turned to page 4th, after the results of the data are the same.
At that time I felt very puzzled, impossible ah, paging components should be good, I may have problems. With doubt, I opened my own IDE, ran a hand on my own machine, and there was a problem.
The problem is to find the problem :
First, the 2 query results of the same SQL print out to the database to execute:
SQL1:
Copy Code code as follows:
SELECT *
From (select t.*, rownum RN
From (select t_e_id, T_e_name, T_e_tel, T_e_areacode
From (select T.eid t_e_id,
T.ename T_e_name,
T.etel T_e_tel,
T.areaid T_e_areacode,
T.biz_delete_time,
Decode (Areaid, ' 0730 ', ' 0 ', ' 1 ') orderseq
From Vr_enterprise t
where T.eid not in (select Eid from T_biz_erelation)
Order by Orderseq, Biz_delete_time nulls last) t
where RowNum < 25)
where RN >= 19
SQL2:
SELECT *
From (select t.*, rownum RN
From (select t_e_id, T_e_name, T_e_tel, T_e_areacode
From (select T.eid t_e_id,
T.ename T_e_name,
T.etel T_e_tel,
T.areaid T_e_areacode,
T.biz_delete_time,
Decode (Areaid, ' 0730 ', ' 0 ', ' 1 ') orderseq
From Vr_enterprise t
where T.eid not in (select Eid from T_biz_erelation)
Order by Orderseq, Biz_delete_time nulls last) t
where RowNum <18)
where RN >= 12
The results show that most rows are the same.
To find out where the problem is, it's only a step-by-step simplification of the SQL to see where the problem is.
So found that the problem occurred in where rownum<18 when the data changed, why add a Where condition results will change?
I don't think so.
No way, had to Baidu, Baidu for a half-day, no one to explain ah ...
Later, colleagues said, try to change the wording, and then changed another way, as follows:
Copy Code code as follows:
SELECT *
From (select t.*, rownum RN
From (select t_e_id, T_e_name, T_e_tel, T_e_areacode
From (select T.eid t_e_id,
T.ename T_e_name,
T.etel T_e_tel,
T.areaid T_e_areacode,
T.biz_delete_time,
Decode (Areaid, ' 0730 ', ' 0 ', ' 1 ') orderseq
From Vr_enterprise t
where T.eid not in (select Eid from T_biz_erelation)
Order by Orderseq, Biz_delete_time nulls (last) t) m
where M.rn >= 1 and M.rn <25
This method is really working together, the effect is to work together, the key is why the previous method does not work? Get to the bottom of the problem.
It seems that Baidu is not good, have to change Google search. Google search always likes to be government wall, no way, the internet had to find a chrome plugin to solve.
Look for it, look for it, in Oracle's Ask Tom, Tom introduced in detail the use of rownum, here http://www.oracle.com/technetwork/issue-archive/2006/06-sep/ O56asktom-086197.html
One important thing about using this pagination query is that the order BY statement should order by something unique. If what you are ordering by isn't unique, you are should add something to the "end of" the order
See such a word, the original with order by the time also need to increase the order by the uniqueness, otherwise rownum will have problems. Wow hahaha, too happy, kungfu do not pay, find the crux of the problem, of course, a good solution.
This right when the experience is a mistake, I hereby record down, it seems that Google is a lot more powerful than Baidu Ah, to learn English well, otherwise encountered problems did not solve.
