SQL行号排序和分页(SQL查询中插入行号 自定义分页
网络编程 2021-07-05 13:42www.168986.cn编程入门
如何在SQL中对行进行动态编号,加行号这个问题,在数据库查询中,是经典的问题
（一）行号显示和排序
1.SQL Server的行号
A.SQL 2000使用identity(int,1,1)和临时表，可以显示行号
SELECT
identity(int,1,1) AS ROWNUM,
[DataID]
INTO #1
FROM DATAS
order by DataID;
SELECT FROM #1
B.SQL 2005提供一个很好用的函数row_number()，
可以直接用来显示行号，也可以使用SQL 2000的identity
SELECT
row_number()over(ORDER BY DataID) AS ROWNUM,
[DataID]
FROM DATAS;
这里如果添加排序功能，则先排序再添加行号
2.ORACLE的行号显示
使用ROWNUM
SELECT
ROWNUM,
[DataID]
FROM DATAS
order by DataID
注意先加行号再排序,如果想排序好再加行号就要使用子查询
3.取前n条数据
A.SQL版
select n [DataID] from DATAS
B.ORACLE版
SELECT
[DataID]
FROM DATAS where ROWNUM<=n
其中，n>=1
ORACLE的ROWNUM不能应用于大于，只能 ROWNUM= 1, 或者<= 大于1 的自然数
(二)SQL分页的几种方式
以每页10条数据为例，查询第三页数据，即21-30这些记录
1.分页方案一(利用Not In和SELECT TOP分页)
语句形式
SELECT TOP 10
FROM DATAS
WHERE DataID NOT IN
(SELECT TOP 20 DataID
FROM DATAS
ORDER BY DataID)
ORDER BY DataID
2.分页方案二(利用ID大于多少和SELECT TOP分页）
语句形式
SELECT TOP 10
FROM DATAS
WHERE ID >
(SELECT MAX(DataID)
FROM (SELECT TOP 20 DataID
FROM DATAS
ORDER BY DataID) AS T)
ORDER BY DataID
3.分页方案三
select 10 DataID from
(SELECT 30
[DataID]
FROM DATAS
order by dataid desc) A
ORDER BY DataID
4.分页方案四(利用SQL的游标存储过程分页)
create procedure SqlPager
@sql nvarchar(8000), --查询字符串
@curpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P int, --P是游标的id
@rowcount int
exec sp_cursoropen @P output,@sql,@scrollopt=1,@opt=1, @rowcount=@rowcount output
select ceiling(1.0@rowcount/@pagesize) as 总页数,@rowcount as 总行数,@curpage as 当前页
set @curpage=(@curpage-1)@pagesize+1
exec sp_cursorfetch @P,16,@curpage,@pagesize
exec sp_cursorclose @P
set nocount off
方法整理如下
代码基于pubs样板数据库
在SQL中,一般就这两种方法.
1.使用临时表
可以使用select into 创建临时表,在第一列,加入Identify(int,1,1)作为行号,
这样在产生的临时表中,结果集就有了行号.也是目前效率最高的方法.
这种方法不能用于视图
set nocount on
select IDentify(int,1,1) 'RowOrder',au_lname,au_fname into #tmp from authors
select frm #tmp
drop table #tmp
2.使用自连接
不用临时表,在SQL语句中,动态的进行排序.这种方法用到的连接是自连接,连接关系一般是
大于,
select rank=count(), a1.au_lname, a1.au_fname
from authors a1 inner join authors a2 on a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
order by count()
运行结果:
rank au_lname au_fname
----------- ---------------------------------------- --------------------
1 Ben Abraham
2 Blotchet-Halls Reginald
3 Carson Cheryl
4 DeFrance Michel
5 del Castillo Innes
6 Dull Ann
7 Greene Morningstar
... ....
缺点:
1.使用自联接，所以该方法不适用于处理大量行。它适用于处理几百行。
对于大型表，一定要使用索引以避免进行大范围的搜索,或用第一种方法.
2.不能正常处理重复值。当比较重复值时，会出现不连续的行编号。
如果不希望出现这种现象，可以在电子表格中插入结果时隐藏排序列，而是使用电子表格编号。
或用第一种方法
优点:
这些查询可以用于视图和结果格式设置中
在结果集中插入了行号，现在就可以将结果集合缓存起来，然后使用DataView，加入过滤条件
RowNum>PageIndexPageSize And RowNum<=(PageIndex+1)PageSize
就能实现快速的分页，而且不论你的页面数据绑定控件是什么（DataList,DataGrid,还是Repeate都可以）。
如果你使用的是DataGrid，那么建议不要使用这种技术。因为DataGrid的分页效率和它差不多。
1.SQL Server的行号
A.SQL 2000使用identity(int,1,1)和临时表，可以显示行号
SELECT
identity(int,1,1) AS ROWNUM,
[DataID]
INTO #1
FROM DATAS
order by DataID;
SELECT FROM #1
B.SQL 2005提供一个很好用的函数row_number()，
可以直接用来显示行号，也可以使用SQL 2000的identity
SELECT
row_number()over(ORDER BY DataID) AS ROWNUM,
[DataID]
FROM DATAS;
这里如果添加排序功能，则先排序再添加行号
2.ORACLE的行号显示
使用ROWNUM
SELECT
ROWNUM,
[DataID]
FROM DATAS
order by DataID
注意先加行号再排序,如果想排序好再加行号就要使用子查询
3.取前n条数据
A.SQL版
select n [DataID] from DATAS
B.ORACLE版
SELECT
[DataID]
FROM DATAS where ROWNUM<=n
其中，n>=1
ORACLE的ROWNUM不能应用于大于，只能 ROWNUM= 1, 或者<= 大于1 的自然数
(二)SQL分页的几种方式
以每页10条数据为例，查询第三页数据，即21-30这些记录
1.分页方案一(利用Not In和SELECT TOP分页)
语句形式
代码如下:
SELECT TOP 10
FROM DATAS
WHERE DataID NOT IN
(SELECT TOP 20 DataID
FROM DATAS
ORDER BY DataID)
ORDER BY DataID
2.分页方案二(利用ID大于多少和SELECT TOP分页）
语句形式
代码如下:
SELECT TOP 10
FROM DATAS
WHERE ID >
(SELECT MAX(DataID)
FROM (SELECT TOP 20 DataID
FROM DATAS
ORDER BY DataID) AS T)
ORDER BY DataID
3.分页方案三
代码如下:
select 10 DataID from
(SELECT 30
[DataID]
FROM DATAS
order by dataid desc) A
ORDER BY DataID
4.分页方案四(利用SQL的游标存储过程分页)
代码如下:
create procedure SqlPager
@sql nvarchar(8000), --查询字符串
@curpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P int, --P是游标的id
@rowcount int
exec sp_cursoropen @P output,@sql,@scrollopt=1,@opt=1, @rowcount=@rowcount output
select ceiling(1.0@rowcount/@pagesize) as 总页数,@rowcount as 总行数,@curpage as 当前页
set @curpage=(@curpage-1)@pagesize+1
exec sp_cursorfetch @P,16,@curpage,@pagesize
exec sp_cursorclose @P
set nocount off
方法整理如下
代码基于pubs样板数据库
在SQL中,一般就这两种方法.
1.使用临时表
可以使用select into 创建临时表,在第一列,加入Identify(int,1,1)作为行号,
这样在产生的临时表中,结果集就有了行号.也是目前效率最高的方法.
这种方法不能用于视图
代码如下:
set nocount on
select IDentify(int,1,1) 'RowOrder',au_lname,au_fname into #tmp from authors
select frm #tmp
drop table #tmp
2.使用自连接
不用临时表,在SQL语句中,动态的进行排序.这种方法用到的连接是自连接,连接关系一般是
大于,
代码如下:
select rank=count(), a1.au_lname, a1.au_fname
from authors a1 inner join authors a2 on a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
order by count()
运行结果:
rank au_lname au_fname
----------- ---------------------------------------- --------------------
1 Ben Abraham
2 Blotchet-Halls Reginald
3 Carson Cheryl
4 DeFrance Michel
5 del Castillo Innes
6 Dull Ann
7 Greene Morningstar
... ....
缺点:
1.使用自联接，所以该方法不适用于处理大量行。它适用于处理几百行。
对于大型表，一定要使用索引以避免进行大范围的搜索,或用第一种方法.
2.不能正常处理重复值。当比较重复值时，会出现不连续的行编号。
如果不希望出现这种现象，可以在电子表格中插入结果时隐藏排序列，而是使用电子表格编号。
或用第一种方法
优点:
这些查询可以用于视图和结果格式设置中
在结果集中插入了行号，现在就可以将结果集合缓存起来，然后使用DataView，加入过滤条件
RowNum>PageIndexPageSize And RowNum<=(PageIndex+1)PageSize
就能实现快速的分页，而且不论你的页面数据绑定控件是什么（DataList,DataGrid,还是Repeate都可以）。
如果你使用的是DataGrid，那么建议不要使用这种技术。因为DataGrid的分页效率和它差不多。
编程语言
- 如何快速学会编程 如何快速学会ug编程
- 免费学编程的app 推荐12个免费学编程的好网站
- 电脑怎么编程：电脑怎么编程网咯游戏菜单图标
- 如何写代码新手教学 如何写代码新手教学手机
- 基础编程入门教程视频 基础编程入门教程视频华
- 编程演示：编程演示浦丰投针过程
- 乐高编程加盟 乐高积木编程加盟
- 跟我学plc编程 plc编程自学入门视频教程
- ug编程成航林总 ug编程实战视频
- 孩子学编程的好处和坏处
- 初学者学编程该从哪里开始 新手学编程从哪里入
- 慢走丝编程 慢走丝编程难学吗
- 国内十强少儿编程机构 中国少儿编程机构十强有
- 成人计算机速成培训班 成人计算机速成培训班办
- 孩子学编程网上课程哪家好 儿童学编程比较好的
- 代码编程教学入门软件 代码编程教程