我想将一张表中的数据分段读取(第一次读取1行-1000行 第二次读取 1001行-2000行)。在MYSQL下我知道可以使用limit就可以。可是在SQL SERVER下如何写这个SQL语句呢??
解决方案 »
- 请问如何制作chm 文件 ????????????
- [召唤帖子]召唤D版里在济南工作的朋友[召唤帖子]
- +++如何编程获得本地机上已经配置好的Oracle服务名+++
- 搞错没有,版主,我把我的贴子都结了,缺仍扣了我的信誉分---叫冤
- 帐龄的分析问题,有兴趣的朋友进来看看好了。
- 关于delphi+sql server的问题。
- delphi6delphi7代码编写器下面有个DIAGRAM页可以写东西的,是干什么用的
- delphi 如何生成目录
- DOS下面的EXE怎么改标题。
- 怎样在DELPHI下编写DCOM程序?具体是如何在CLIENT调用SERVER端对象的方法?是不是先在类型库中声明方法,再在单元中实现?
- 如何取dll 的首地址
- Delphi修改了哪些系统参数
1001-2000:select top 1000 * from (select top 2000 * from table order by id asc) table_别名 order by id desc
FReadQuery.LockType:=ltBatchOptimistic;
FReadQuery.CacheSize:=1000;
FReadQuery.CursorLocation:=clUseServer;
FReadQuery.CursorType:=ctStatic;
/*
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句,不带Where
7.Group :Group语句,不带Group ByEXEC Pagination 'CRM_Client','cusCode','LastLinkDate DESC',1,20,
'cusCode,cusName,userId,productId,levelCode,cusProvId,cusCityId,deveStatus,dredgeStat',
' cusNStatus =60 and productId = 30 ',''
***************************************************************/
ALTER PROCEDURE Pagination
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL,
@total INT OUTPUT
)
AS
BEGIN
/*默认排序*/
IF @Sort IS NULL or @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
DECLARE @totalSQL nvarchar(200) SET @totalSql= '' IF LEN(@Filter) > 0 BEGIN
SET @totalSQL= 'SELECT @TotalCount=count('+@PrimaryKey+') FROM ' + @Tables+' WHERE ' + @Filter END ELSE BEGIN SET @totalSQL= 'SELECT @TotalCount=count('+@PrimaryKey+') FROM ' + @Tables END
EXEC sp_executesql @totalSQL,N'@TotalCount int=0 output',@total output
/*设定排序语句.*/ IF CHARINDEX('DESC',@Sort)>0 BEGIN SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<=' END ELSE BEGIN --IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>=' END IF CHARINDEX('.', @strSortColumn) > 0 BEGIN SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn)) END ELSE BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END Select @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype
Where o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(300)
DECLARE @strSimpleFilter varchar(300)
DECLARE @strGroup varchar(100) /*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != '' BEGIN SET @strFilter = ' Where ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' ' END ELSE BEGIN SET @strSimpleFilter = ''
SET @strFilter = '' END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*执行查询语句*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
Select ' + @Fields + ' FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
'
)END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO