Oracle row_number() over()解析函数高效实现分页

发布网友发布时间：2022-04-28 15:38

共2个回答

懂视网时间：2022-05-01 00:42

1：首先是

select ROW_NUMBER() over(order by id asc) as ‘rowNumber‘, * from table1

生成带序号的集合

2：再查询该集合的第 1 到第 5条数据

    select * from
    (select ROW_NUMBER() over(order by id asc) as ‘rowNumber‘, * from table1) as temp
    where rowNumber between 1 and 5

完整的Sql语句

declare @pagesize int; declare @pageindex int; set @pagesize = 3

set @pageindex = 1; --第一页

select * from (select ROW_NUMBER() over(order by id asc) as ‘rowNumber‘, * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)

set @pageindex = 2; --第二页

select * from (select ROW_NUMBER() over(order by id asc) as ‘rowNumber‘, * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)

set @pageindex = 3; --第三页

select * from (select ROW_NUMBER() over(order by id asc) as ‘rowNumber‘, * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)

set @pageindex = 4;--第四页

select * from (select ROW_NUMBER() over(order by id asc) as ‘rowNumber‘, * from table1) as temp where rowNumber between (((@pageindex-1)*@pagesize)+1) and (@pageindex*@pagesize)

下面我们来写个存储过程分页

------------------------------

Alter Procedure PagePager

@TableName varchar(80),

@File varchar(1000),---

@Where varchar(500),---带and连接

@OrderFile varchar(100), -- 排序字段

@OrderType varchar(10),--asc:顺序，desc:倒序

@PageSize varchar(10), --

@PageIndex varchar(10) --

if(ISNULL(@OrderFile, ‘‘) = ‘‘)

begin

set @OrderFile = ‘ID‘;

end

if(ISNULL(@OrderType,‘‘) = ‘‘)

begin

set @OrderType = ‘asc‘

end

if(ISNULL(@File,‘‘) = ‘‘)

begin

set @File = ‘*‘

end

declare @select varchar(8000)

set @select = ‘ select ‘ + @File + ‘ from ( select *,ROW_NUMBER() over(order by ‘ + @OrderFile + ‘ ‘+ @OrderType + ‘) as ‘‘rowNumber‘‘ from ‘ + @TableName + ‘ where 1=1 ‘ + @Where + ‘ ) temp where rowNumber between (((‘ + @PageIndex + ‘ - 1) * ‘ + @PageSize + ‘)+1) and (‘ + @PageIndex + ‘*‘+ @PageSize+‘)‘

exec(@select)

用oracle中的Row_Number实现分页

标签：

热心网友时间：2022-04-30 21:50

复制代码
代码如下:
create
table
T_NEWS
(
ID
NUMBER,
N_TYPE
VARCHAR2(20),
N_TITLE
VARCHAR2(30),
N_COUNT
NUMBER
)
prompt
Disabling
triggers
for
T_NEWS...
alter
table
T_NEWS
disable
all
triggers;
prompt
Loading
T_NEWS...
insert
into
T_NEWS
(ID,
N_TYPE,
N_TITLE,
N_COUNT)
values
(1,
'IT',
'爱it1',
100);
insert
into
T_NEWS
(ID,
N_TYPE,
N_TITLE,
N_COUNT)
values
(2,
'体育',
'爱体育1',
10);
insert
into
T_NEWS
(ID,
N_TYPE,
N_TITLE,
N_COUNT)
values
(3,
'体育',
'爱体育2',
30);
insert
into
T_NEWS
(ID,
N_TYPE,
N_TITLE,
N_COUNT)
values
(4,
'IT',
'爱it2',
300);
insert
into
T_NEWS
(ID,
N_TYPE,
N_TITLE,
N_COUNT)
values
(5,
'IT',
'爱it3',
200);
insert
into
T_NEWS
(ID,
N_TYPE,
N_TITLE,
N_COUNT)
values
(6,
'体育',
'爱体育3',
20);
insert
into
T_NEWS
(ID,
N_TYPE,
N_TITLE,
N_COUNT)
values
(7,
'体育',
'爱体育4',
60);
commit;
第一步：我先用rownum
--分页
row_number，不是rownum
--根据n_count从大到小排列，每页3条
SELECT
ROWNUM
r,t.*
FROM
t_news
t
WHERE
ROWNUM<=3
ORDER
BY
t.n_count
DESC
--问题：为什么order
by以后，行号是乱的？
SELECT
ROWNUM
r,t.*
FROM
t_news
t
--原因：先分配了行号，再根据n_count排序
--所以必须排序，再生成行号
SELECT
ROWNUM
r,t.*
FROM
(
SELECT
t.*
FROM
t_news
t
ORDER
BY
t.n_count
DESC
)
t
--分页
--err
SELECT
ROWNUM
r,t.*
FROM
(
SELECT
t.*
FROM
t_news
t
ORDER
BY
t.n_count
DESC
)
t
WHERE
r
between
1
AND
3
--第1页
SELECT
ROWNUM
r,t.*
FROM
(
SELECT
t.*
FROM
t_news
t
ORDER
BY
t.n_count
DESC
)
t
WHERE
ROWNUM
between
1
AND
3
--第2页
SELECT
ROWNUM
r,t.*
FROM
(
SELECT
t.*
FROM
t_news
t
ORDER
BY
t.n_count
DESC
)
t
WHERE
ROWNUM
between
4
AND
6
--error:
ROWNUM必须从1开始！
SELECT
k.*
FROM
(
SELECT
ROWNUM
r,t.*
FROM
(
SELECT
t.*
FROM
t_news
t
ORDER
BY
t.n_count
DESC
)
t
)
k
WHERE
r
BETWEEN
4
AND
6
--麻烦，效率低！
*****第二步:我用row_number()
over()函数
select
t2.*
from
(select
t.*,row_number()over(order
by
t.n_count
desc)
orderNumber
from
t_news
t
order
by
t.n_count
desc)t2
where
orderNumber
between
1and
3;
*****************************************************************************************************************************************88
SELECT
*
FROM
(
SELECT
t.*,row_number()
over(ORDER
BY
n_count
DESC)
r
FROM
t_news
t
ORDER
BY
t.n_count
DESC
)
t
WHERE
r
BETWEEN
4
AND
6
--通用语法：
解析函数()
over(partition
by
字段
order
by
字段)