今天根据项目需求用php连oracle做一个消费记录展示,在分页的时候踩了不少坑
1、oracle不同于musql,它没有limit
2、oracle的日期直接查出来和我们常见的YY-mm-dd HH:ii:ss有区别,需要to_char(l.opt_time,'YYYY-MM-DD HH24:MI:SS') as ymd_time
3、使用行号rownum分页时,语句如果是
select * from (select t.*,rownum as rn from table1 t where uid=*** and status=1 order by create_time desc) where rn between 1 and 5;
这样会有问题,子查询语句会先生成行号再进行desc排序,完全是白排序了好不好
4、见识到3的问题以后我又改了下语句,然后跳进了另一个大坑
select tab_.*,rownum from (select * from table1 where uid=*** and status=1 order by create_time desc) tab_ where rownum between 1 and 5;
这回分行正常了,但是每次查询带1的行才有值,什么意思呢?就是 between 1 and 5有值,between 2 and 5 就是空,rownum=1 有值,rownum=2就是空。哇,气得抓耳挠腮
5、百度了一下终于找到正确的写法了,是mysql里没见过的写法(也可能是我见识浅薄)
select tab_.* from(select l.*,ROW_NUMBER() OVER (ORDER BY create_time desc) rn from table1 l
where user_id = *** and status = 1
) tab_ where rn between ".$start." and ".$end;