I haven't written anything for a long time, because I haven't encountered any problems for a long time. Once and again, I think that Oracle's omnipotent paging is quite refreshing. Today I finally encountered a problem.
Oracle can use SQL statements as parameters to construct a universal paging SQL statement.
"Select * from (select row _. *, rownum _ from (" + SQL ") row _ where
Rownum <= ?) Where rownum _>? "
Oracle uses nested layer-3 query statements and rownum to implement paging, which is the fastest way in Oracle,
If only one or two layers of query statement rownum cannot support order.
For example:
SQL = select * from admin
Concatenate select * from (select row _. *, rownum _ from (select * from admin) row _ where
Rownum <= 110) where rownum _> 100
No problem
But today, a self-recursive parent-child node table is faulty.
The dept table structure is as follows:
Deptid parentid name
1 0 direct Brigade
2 1 directly under the brigade 1 squadron
Entry SQL =
Select
P. tb_parkingbus_id id,
P. tb_eventinfo_id,
P. ajbh,
T. bustypename,
R. resultname,
.....
Dept. dept_name,
Deptoffice. dept_name
From tb_parkingbus p, 'Vehicle table
Tb_1_bustype t, 'Vehicle type table
Tb_cfgbusresult r, 'Vehicle processing result table
Dept, 'level 1 Department table
Dept deptoffice 'level 2 Department table
Where t. bustypecode = p. bustypecode and r. result = p. result and p. sts = 'A' and dept. dept_id = p. dept_id
And dept. parentid = deptoffice. dept_id // Level 2 Department parent node ID = level 1 Department ID
And
P. dept_id = '20140901'
Order by p. ajbh desc
This SQL statement is correct. The SQL statements can be executed normally, but the columns that are not explicitly defined will appear after the SQL statements are substituted into the universal paging SQL statements.
It seems that there is no way