分页语句:
go
1、利用not in
1)select top 10 * from tablename
where id not in (select top 20 id form tablename2 order by id) order by id
2、根据最大最小数
select * from tablename
where id in (select top 40 id from tablename order by id)
and id not in (select top 20 id from tablename order by id)
3、最大最小另一种写法
select top 2 * from (select top 4 * from [LoginLog] order by LoginId) as t
order by LoginId desc
4、 利用倒序和正序
SELECT TOP 3 * FROM [LoginLog]
WHERE ([LoginId] >(SELECT MAX([LoginId])FROM(SELECT TOP 4 [LoginId]FROM [LoginLog]ORDER BY [LoginId]) AS T))ORDER BY [LoginId]
5 、利用Row_Number()函数
1)select * from (select Row_Number() over
(Order by UserId) as RowId ,* from tablename ) U
where U.RowId between 3 and 6
6、定位法
declare @page int
declare @size intdeclare @rowcount intdeclare @temp intdeclare @beginid intdeclare @endid intset @page =2set @size = 3set nocount onselect @rowcount = COUNT(1) from [LoginLog] with(nolock)SET @temp = (@page - 1) * @size + 1 --4SET ROWCOUNT @temp --SELECT @beginid = [LoginId] FROM [LoginLog] WITH(NOLOCK)--5ORDER BY [LoginId] DESCSET @temp = @page * @size --6SET ROWCOUNT @temp --SELECT @endid = [LoginId] FROM [LoginLog] WITH(NOLOCK)--3ORDER BY [LoginId] DESC
set rowcount 07、游标分页
set nocount offselect * from [LoginLog] where LoginId between @endid and @beginid
go
create procedure XiaoZhengGe@sqlstr nvarchar(4000), --查询字符串@currentpage int, --第N页@pagesize int --每页行数asset nocount ondeclare @P1 int, --P1是游标的id@rowcount intexec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount outputselect ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页set @currentpage=(@currentpage-1)*@pagesize+1exec sp_cursorfetch @P1,16,@currentpage,@pagesizeexec sp_cursorclose @P1set nocount off
8、大数据分页
SET ANSI_NULLS ON
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ONGO--分页存储过程CREATE PROCEDURE [dbo].[sp_Paging](@Tables nvarchar(1000), --表名/视图名@PrimaryKey nvarchar(100), --主键@Sort nvarchar(200) = NULL, --排序字段(不带order by)@pageindex int = 1, --当前页码@PageSize int = 10, --每页记录数@Fields nvarchar(1000) = N'*', --输出字段@Filter nvarchar(1000) = NULL, --where过滤条件(不带where)@Group nvarchar(1000) = NULL, --Group语句(不带Group By)@TotalCount int OUTPUT --总记录数)ASDECLARE @SortTable nvarchar(100)DECLARE @SortName nvarchar(100)DECLARE @strSortColumn nvarchar(200)DECLARE @operator char(2)DECLARE @type nvarchar(100)DECLARE @prec int--设定排序语句IF @Sort IS NULL OR @Sort = ''SET @Sort = @PrimaryKeyIF CHARINDEX('DESC',@Sort)>0BEGINSET @strSortColumn = REPLACE(@Sort, 'DESC', '')SET @operator = '<='ENDELSEBEGINSET @strSortColumn = REPLACE(@Sort, 'ASC', '')SET @operator = '>='ENDIF CHARINDEX('.', @strSortColumn) > 0BEGINSET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))ENDELSEBEGINSET @SortTable = @TablesSET @SortName = @strSortColumnEND--设置排序字段类型和精度SELECT @type=t.name, @prec=c.prec FROM sysobjects oJOIN syscolumns c on o.id=c.idJOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortNameIF CHARINDEX('char', @type) > 0SET @type = @type + '(' + CAST(@prec AS varchar) + ')'DECLARE @strPageSize nvarchar(50)DECLARE @strStartRow nvarchar(50)DECLARE @strFilter nvarchar(1000)DECLARE @strSimpleFilter nvarchar(1000)DECLARE @strGroup nvarchar(1000)IF @pageindex <1SET @pageindex = 1SET @strPageSize = CAST(@PageSize AS nvarchar(50))--设置开始分页记录数SET @strStartRow = CAST(((@pageindex - 1)*@PageSize + 1) AS nvarchar(50))--筛选以及分组语句IF @Filter IS NOT NULL AND @Filter != ''BEGINSET @strFilter = ' WHERE ' + @Filter + ' 'SET @strSimpleFilter = ' AND ' + @Filter + ' 'ENDELSEBEGINSET @strSimpleFilter = ''SET @strFilter = ''ENDIF @Group IS NOT NULL AND @Group != ''SET @strGroup = ' GROUP BY '--计算总记录数DECLARE @TotalCountSql nvarchar(1000)SET @TotalCountSql=N'SELECT @TotalCount=COUNT(*)' +N' FROM ' + @Tables + @strFilterEXEC sp_executesql @TotalCountSql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT--执行查询语句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 + '')
9、暂时保存
ALTER PROCEDURE [dbo].[CT_Append_Search]
(
@VC_A_SNNO varchar(50),
@VC_OC_UserName varchar(50),
@VC_OC_Company varchar(50),
@VC_A_CardNO varchar(50),
@CardType int,
@VC_A_AppendType varchar(50),
@VC_TicketType varchar(50),
@VC_TicketNO varchar(50),
@StartDate varchar(50),
@EndDate varchar(50),
@PageIndex int,
@TotalRecords int
)
AS
BEGIN
DECLARE @Page int
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
SET @Page = (@PageIndex - 1)
-- First set the rowcount
SET @RowsToReturn = @TotalRecords * (@Page + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @TotalRecords * @Page
SET @PageUpperBound = @PageLowerBound + @TotalRecords + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
VC_A_SNNO varchar(50)
)
Declare @ConSQL varchar(2000)
Declare @StrSQL varchar(2000)
set @StrSQL='INSERT INTO #PageIndex (VC_A_SNNO) SELECT [VC_A_SNNO] FROM [CT_Append] a LEFT OUTER JOIN
CT_OuterCard b ON a.VC_A_CardNO = b.VC_OC_CardNO LEFT OUTER JOIN
CT_InhouseCard c ON a.VC_A_CardNO = c.VC_IC_CardNO where a.RecordNO<>0'
set @ConSQL=''
if(@VC_A_SNNO<>'')
begin
set @ConSQL=@ConSQL + ' and a.VC_A_SNNO like ''%' + @VC_A_SNNO +'%'''
end
if(@VC_OC_UserName<>'')
begin
set @ConSQL=@ConSQL + ' and b.VC_OC_UserName like ''%' + @VC_OC_UserName+'%'''
end
if(@VC_OC_Company<>'')
begin
set @ConSQL=@ConSQL + ' and b.VC_OC_Company like ''%' + @VC_OC_Company+'%'''
end
if(@VC_A_CardNO<>'')
begin
set @ConSQL=@ConSQL + ' and a.VC_A_CardNO like ''%' + @VC_A_CardNO+'%'''
end
if(@CardType>0)
begin
set @ConSQL=@ConSQL + ' and a.I_A_CardType=' + cast(@CardType as varchar(2))
end
if(@VC_A_AppendType<>'')
begin
set @ConSQL=@ConSQL + ' and a.VC_A_AppendType=''' + @VC_A_AppendType+''''
end
if(@VC_TicketType<>'')
begin
set @ConSQL=@ConSQL + ' and a.VC_TicketType=''' + @VC_TicketType+''''
end
if(@VC_TicketNO<>'')
begin
set @ConSQL=@ConSQL + ' and a.VC_TicketNO=''' + @VC_TicketNO+''''
end
if(@StartDate<>'')
begin
set @ConSQL=@ConSQL + ' and a.D_A_AppendDateTime>=''' + @StartDate+''''
end
if(@EndDate<>'')
begin
set @ConSQL=@ConSQL + ' and a.D_A_AppendDateTime<=''' + @EndDate+''''
end
Execute(@StrSQL + @ConSQL +' order by a.D_A_AppendDateTime DESC')
SELECT
c.VC_A_SNNO,
[VC_A_AppendType] ,
[VC_A_CardNO] ,
[I_A_CardType] ,
[I_A_PointToOil] ,
[VC_TicketType] ,
[VC_TicketNO] ,
[DE_A_BAmount] ,
[DE_A_AppendAmount] ,
[DE_A_AAmount] ,
[D_A_AppendDateTime] ,
[VC_A_Remark] ,
[VC_A_OperatorNO]
FROM [CT_Append] c , #PageIndex PageIndex
WHERE
c.VC_A_SNNO = PageIndex.VC_A_SNNO AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
Execute('SELECT COUNT(VC_A_SNNO) AS TotalRecords FROM [CT_Append] a LEFT OUTER JOIN
CT_OuterCard b ON a.VC_A_CardNO = b.VC_OC_CardNO LEFT OUTER JOIN
CT_InhouseCard c ON a.VC_A_CardNO = c.VC_IC_CardNO where a.RecordNO<>0' + @ConSQL)
END