我的表结构:
表名:WX_REGULAR_MAINTAIN_WORK
表字段:WORK_ID 、SYSTEM_ID....LAST_EXECUT_TIME、MAINTAIN_PERIOD、DELSIGN
sql语句作用:从前台传递start、limit参数,读取数据表中start行->(start+limit*页数) 行的记录。
string sqlCommand =
@"select * from (select * from WX_REGULAR_MAINTAIN_WORK order by (LAST_EXECUT_TIME+ MAINTAIN_PERIOD) ASC)
where DELSIGN = '0' and rownum <= "+limit+@" and WORK_ID||SYSTEM_ID not in
( select WORK_ID||SYSTEM_ID
from (select * from WX_REGULAR_MAINTAIN_WORK order by (LAST_EXECUT_TIME+ MAINTAIN_PERIOD) ASC)
where DELSIGN = '0' and rownum <="+start+" ) ";
order by 用在from里,是为了在已经按照(LAST_EXECUT_TIME+ MAINTAIN_PERIOD)排序数据集里查找记录。
rownum 是数据库自带的标识符。
第M行->第N行 按照某字段有序读取简单写成:
select * from (select * from a order by a.id) where rownum < = N and a.id not in ( select * from (select * from a order by a.id) where rownum<=M)
实践中出现的问题:
1,Oracle无法在子查询中实现orader by 。总会出现提示“缺少右括号”。
2,rownum 只有 rownum < 值 或是rownum <= 值的方法。没有>或>=。
3,MINUS也可以实现读取第M行至第N行的读取,如果想按照有序排列,只需from 一个有序的select集合。
例如:
(select * from a where rownum<=5)
minus
( select * from a where rownum<4 )
改成:
(select * from (select * from a order by a.id) where rownum<=5 )
minus
(select * from (select * from a order by a.id) where rownum<4)