Heim  >  Artikel  >  Datenbank  >  SQL行号排序和分页(SQL查询中插入行号 自定义分页的另类实现)

SQL行号排序和分页(SQL查询中插入行号 自定义分页的另类实现)

WBOY
WBOYOriginal
2016-06-07 18:07:031066Durchsuche

如何在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 top n [DataID] from DATAS
B.ORACLE版
SELECT
[DataID]
FROM DATAS where ROWNUM其中,n>=1
ORACLE的ROWNUM不能应用于大于,只能 ROWNUM= 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 top 10 DataID from
(SELECT top 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,@ccopt=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 Bennet 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>PageIndex*PageSize And RowNum  就能实现快速的分页,而且不论你的页面数据绑定控件是什么(DataList,DataGrid,还是Repeate都可以)。
  如果你使用的是DataGrid,那么建议不要使用这种技术。因为DataGrid的分页效率和它差不多。
Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn