Question

oracle 按时间排序，然后进行分页的sql改怎么写？急~~~ 表中有（时间字段）

Answer 1

SELECT t.字段名 FROM (
SELECT 字段（可以是多表关联的字段） ,
row_number() over(order by 时间字段 desc) r
FROM 表名 where 过滤条件 ) t
where t.r <= (每页显示条数*要查询的页) and t.r > (每页显示条数*(要查询的页-1))
有疑问可以再问
望采纳

追问

SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (select repairyardcode,repairyardname,comcode,channelcode,cooname,cooaddress,coozipcode,coocontact,coophone,coofax,picname,pictel,picphone,picemail,remark,flag,acomcode,acomname,repaircode,modelname,longitude,latitude,operatedate from prpdrepairyard where 1=1 order by operatedate desc) row_ WHERE rownum 0

追答

你这写的是什么意思，这个没有分页吧，而且你不要用*来查询，会影响查询性能的

追问

这个是分页而且能查出来，不过就是没有按整体的时间排序

追答

你这个只能手动改分页参数，如果我要每页显示5条数据，查看第2页的数据，你不是得一直改，你完成可以直接套用我上面给你写的那个，查询效率也肯定比你的高得多

Answer 2

SELECT
*
FROM
(
select
rownum AS NO,
表.*
from
表
order by
表.时间字段
) subQuery
WHERE
NO between 开始行 AND 结束行;

追问

SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (select repairyardcode,repairyardname,comcode,channelcode,cooname,cooaddress,coozipcode,coocontact,coophone,coofax,picname,pictel,picphone,picemail,remark,flag,acomcode,acomname,repaircode,modelname,longitude,latitude,operatedate from prpdrepairyard where 1=1 order by operatedate desc) row_ WHERE rownum 0？？

追答

SELECT
*
FROM
(
select
rownum rownum_
repairyardcode,repairyardname,comcode,
channelcode,cooname,cooaddress,
coozipcode,coocontact,coophone,
coofax,picname,pictel,picphone,
picemail,remark,flag,
acomcode,acomname,repaircode,
modelname,longitude,latitude,
operatedate
from prpdrepairyard
where 1=1
order by operatedate desc
) row_
WHERE
rownum_ >= 1 AND rownum_ <= 10