sql分页处理

With tempTb AS
(
   Select *,ROW_NUMBER() OVER(order by ID ASC) AS RowNumber FROM TankInfo where StationID='10001'
)
Select * FROM  tempTb  where RowNumber>=1 AND RowNumber<=3

-- ================================================
-- 获取所有设备的信息
-- 分页处理
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE ups_GetAllRenewInfo
	-- Add the parameters for the stored procedure here
	@StationID varchar(10),
	@PageNo  int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	with tempDB as
	(
		select ServerStatus,GpsID,TankName,FirstOnlineTime,ExpireTime,CardNo,'未指定' as ContructionName  FROM  TankInfo 
		where NOT EXISTS (select * from Construction  
		where Construction.ConstructionID = TankInfo.ConstructionID)and  TankInfo.StationID = @StationID
		union
		Select  ServerStatus,GpsID,TankName,FirstOnlineTime,ExpireTime,CardNo,ConstructionName 
		FROM TankInfo,Construction where 
		TankInfo.ConstructionID = Construction.ConstructionID and TankInfo.StationID = @StationID
	)
	Select *,ROW_NUMBER() OVER(order by GpsID ASC) AS RowNumber  into #tempTable FROM tempDB 

	Select * FROM  #tempTable  where RowNumber>=(@PageNo-1)*2+1 AND RowNumber<= @PageNo*2

	drop table #tempTable

END
GO

-----------------------------------------------
--exec ups_GetAllRenewInfo '10001',1