获取下100条记录 [英] Getting next 100 records

问题描述

我想在查询中选择下100条记录，我应该使用什么，

就像我有查询
选择RegNo，PatientName，FCNo，RelationName，G.Description Gender，DATEDIFF(YYYY，DOB，GETDATE())''Age''，R.从PRS.dbo.tblRegistration中删除R左外部联接TKC.dbo.tblGender G打开R.GenderID = G.GenderID

现在，我每次如何获取下100条记录.

i want to select next 100 records in a query, what should i use,

like i have query
Select RegNo, PatientName, FCNo, RelationName, G.Description Gender, DATEDIFF(YYYY,DOB,GETDATE())''Age'', R.Deleted from PRS.dbo.tblRegistration R LEFT OUTER JOIN TKC.dbo.tblGender G ON R.GenderID=G.GenderID

now how i get next 100 records every time.

推荐答案

一种选择是执行以下操作:

One option is to do the following:

DECLARE @PageSize INT
DECLARE @Page INT
SELECT  @PageSize = 100,@Page = 2

;WITH temp_table  AS(
Select RegNo, PatientName, FCNo, RelationName, G.Description Gender, DATEDIFF(YYYY,DOB,GETDATE())'Age', R.Deleted, ROW_NUMBER() OVER(ORDER BY RegNo) ID  from PRS.dbo.tblRegistration R LEFT OUTER JOIN TKC.dbo.tblGender G ON R.GenderID=G.GenderID
)
SELECT  * FROM temp_table WHERE ID BETWEEN ((@Page - 1) * @PageSize + 1)  AND (@Page * @PageSize)

这可以在SP中，您可以将pagesize和page作为参数传递给SP.

This can be in an SP, to which you can pass pagesize and page as parameters..

这是从db获取下一条记录的简单方法.

This is the simple way for getting next records from db..

-- you need to pass the @PAGENO for geting that part record
-- OR @RECORDNO is the variable which use for geting that much record..
-- here you need 100 record so, it will be seted to 100
-- you can use it as your way


DECLARE @PAGENO INT
DECLARE @RECORDNO INT
SET @PAGENO = 0
SET @RECORDNO = 100

SELECT TOP(@RECORDNO) * FROM OrderMaster
WHERE OrderMaster.OrderID NOT IN (
SELECT TOP (@PAGENO * @RECORDNO) OrderMaster.OrderID FROM OrderMaster)

这篇关于获取下100条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！