数据库分页语句

最新推荐文章于 2023-12-15 14:28:28 发布

原创最新推荐文章于 2023-12-15 14:28:28 发布 · 139 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #MySQL #SQL Server #SQL

Hibernate 专栏收录该内容

15 篇文章

订阅专栏

本文详细介绍了三种不同的数据库分页实现方法，包括Oracle数据库的三层嵌套查询方式、MySQL的LIMIT关键字使用技巧以及SQL Server中利用ROW_NUMBER()进行分页的具体步骤。这些技术对于提高大型数据集的查询效率至关重要。

Oracle 数据库分页

三层嵌套：

select
        * 
    from
        ( select
            row_.*,
            rownum rownum_ 
        from
            ( select
                this_.id as id63_3_,
                this_.amount as amount63_3_,
                this_.arrival_time as arrival3_63_3_,
                this_.coal_type as coal18_63_3_,
                this_.coal_source as coal15_63_3_,
                this_.diggings as diggings63_3_,
                this_.loadstation as loadsta17_63_3_,
                this_.manufactory as manufac16_63_3_,
                this_.lod_coal_type as lod4_63_3_,
                this_.old_stack_id as old5_63_3_,
                this_.port_sell_id as port6_63_3_,
                this_.remark as remark63_3_,
                this_.source_id as source8_63_3_,
                this_.sourcebz_type as sourcebz9_63_3_,
                this_.stack_id as stack10_63_3_,
                this_.train_no as train11_63_3_,
                this_.trainbusiness_id as trainbu12_63_3_,
                this_.vessel_id as vessel13_63_3_,
                coalclass2_.id as id40_0_,
                coalclass2_.abbr as abbr40_0_,
                coalclass2_.is_activated as is3_40_0_,
                coalclass2_.name as name40_0_,
                coalclass2_.parent_id as parent5_40_0_,
                coalclass2_.parent_name as parent6_40_0_,
                coalclass2_.remark as remark40_0_,
                coalclass2_.type as type40_0_,
                coalclass2_.type_ii as type9_40_0_,
                coalsource3_.id as id41_1_,
                coalsource3_.abbr as abbr41_1_,
                coalsource3_.name as name41_1_,
                coalsource3_.remark as remark41_1_,
                loadstatio4_.id as id48_2_,
                loadstatio4_.abbr as abbr48_2_,
                loadstatio4_.address as address48_2_,
                loadstatio4_.distance as distance48_2_,
                loadstatio4_.is_activated as is5_48_2_,
                loadstatio4_.name as name48_2_,
                loadstatio4_.remark as remark48_2_,
                loadstatio4_.station_code as station8_48_2_ 
            from
                ts_stack_inventory this_,
                bc_coalclass coalclass2_,
                bc_coal_source coalsource3_,
                bc_loadstation loadstatio4_ 
            where
                this_.coal_type=coalclass2_.id(+) 
                and this_.coal_source=coalsource3_.id(+) 
                and this_.loadstation=loadstatio4_.id(+) 
            order by
                this_.id asc ) row_ ) 
        where
            rownum_ <= ? 
            and rownum_ > ?

MySQL:

select
        topic0_.id as id2_,
        topic0_.categoryId as categoryId2_,
        topic0_.createDate as createDate2_,
        topic0_.title as title2_ 
    from
        topic topic0_ 
    where
        not (exists (select
            msg1_.id 
        from
            Msg msg1_ 
        where
            msg1_.topicId=topic0_.id)) limit ?, ?

SQL Server:

declare @pagesize int,@pageNum int
set @pagesize=10
set @pageNum=2

select * from (
select *,row_number() over(order by 分组字段) rn  
from 表) a
where rn between @pagesize*(@pageNum-1) and @pagesize*(@pageNum)-1