Oracle和SQL 2008分页查询

最新推荐文章于 2025-03-07 23:06:52 发布

原创最新推荐文章于 2025-03-07 23:06:52 发布 · 576 阅读

0 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了在Oracle和SQL Server数据库中实现分页查询的方法。对于Oracle，无论是单表还是多表查询，都通过ROWNUM进行记录编号，并结合WHERE子句实现分页。在SQL Server 2008中，则使用ROW_NUMBER()函数为每条记录分配唯一编号，再通过WHERE子句完成分页。这些技巧有助于提高数据库查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oralce分页查询

对于单表查询：

select *
from (select temp.*,rownum rn
from (select * from t_ap_ap_app) temp)
where rn between 21 and 40;

select rn,temp1.*
from (select temp.*, rownum rn
from (select * from t_ap_ap_app order by app_code desc) temp
where rownum<=40) temp1
where rn>=21;

对于多表查询：

select *
from (select temp.*, rownum rn
from (select app.app_code as app_code, app.app_status as status
from t_tp_app app left join t_tp_app_line appLine
on app.app_code=appLine.app_code) temp
where rownum<=40)
where rn>=21;

SQL 2008分页查询

对于单表查询：

select *
from (select ROW_NUMBER() over(order by temp.code desc) as rownum,temp.*
from (select * from T_AP_ORDER) as temp) as temp1
where temp1.rownum between 21 and 40 ;

对于多表查询：

select *
from(select row_number() over(order by temp.code desc) as rownum,temp.*
from (select od.CODE as code,od.STATUS as status
from T_AP_ORDER as od left join T_AP_ORDER_LINE as odLine
on od.ID=odLine.ORDER_ID) as temp) temp1
where rownum between 21 and 40;