SQL Server returns more accurate paging

Long time, and found a new problem, is the master table paging query. For example: order form and order list, request is Inquiry order, second page, 10 per page

Copy code code as follows:
--use Row_unmber () to implement paging
The result we wanted was 10 orders, but not 10 orders, but 10 details.
--in fact, the child table for the paging, the order is not to display the number of the number is the number of details
-Just because of the results of the primary and child table union queries, the primary and child table records are 1:n relationships, and a master table record has multiple details
SELECT * FROM
(SELECT row_number () over (order by Oi.createdate DESC) as Rownumber,oi.orderseqno, OD. Orderdetailid
From OrderInfo Oi the left JOIN orderdetail od on Oi. Orderseqno=od. Orderseqno
WHERE Oi. Orderseqno like '%2% '
) as O
WHERE RowNumber BETWEEN 20

The result is the following figure

Obviously not 10 orders, but 10 details.

Here's a little improvement on the query, using a cursor

Copy code code as follows:
To solve the above problems, there are the following methods
--1, first query the main table records based on the criteria, and then loop through the C # code, again to the Database tutorial query the details of each master table record, and assign the value to the property
--2, the use of cursors in the stored procedure of the database, is also the first query of the master table records, and then use the cursor loop in the process of querying the child table information, and then in C #
--Centralized processing
Obviously, the latter reduces the transaction cost of the database, one time to obtain the desired data, personally think it is better than the first, welcome everyone to discuss a better way
Note that the type returned by Row_number () is bigint, not int
--The following is the cursor's stored procedure
--Create a primary table temp table
CREATE TABLE #temp
(
RowNumber bigint,
Orderseqno VARCHAR (36),
Goodsname VARCHAR (50),
CompanyName VARCHAR (100)
)
--Create a child table temporary table
CREATE TABLE #detail
(
Orderseqno VARCHAR (36),
Detailid uniqueidentifier,
UnitPrice DECIMAL (12,2),
Qty int
)
--Insert primary table data into primary table temp table
INSERT INTO #temp
SELECT Oo.rownumber, Oo. Orderseqno, Oo. Goodsname, Oo.companyname from
(SELECT row_number () over (order by Oi.createdate DESC) as RowNumber,
Oi. Orderseqno, Oi. Goodsname, Ci.companyname
From OrderInfo oi INNER JOIN companyinfo ci on Oi.companyid=ci.companyid
WHERE Oi. Createdate<getdate ()
) as Oo
WHERE RowNumber BETWEEN 20
--Defining cursors
DECLARE @temp_cursor Cursor
--Assign a value to a cursor
SET @temp_cursor =cursor for SELECT #temp. Orderseqno, #temp. Goodsname from #temp
--Defines the temporary data that is required to be saved during a cursor loop
DECLARE @orderseqno VARCHAR, @goodsname VARCHAR (50)
--Open cursor
OPEN @temp_cursor
FETCH NEXT from @temp_cursor into @orderseqno, @goodsname
--loop cursor, query child table data, and insert child table temporary table
While @ @FETCH_STATUS =0
BEGIN
INSERT into #detail
SELECT od. Orderseqno,od. Orderdetailid, OD. Unitprice,od. Qty
From OrderDetail OD
WHERE od. orderseqno= @orderseqno
FETCH NEXT from @temp_cursor into @orderseqno, @goodsname
End
--Close cursor
Close @temp_cursor
Deallocate @temp_cursor
SELECT * from #temp
SELECT * from #detail
--Delete temporary tables
DROP TABLE #temp
DROP TABLE #detail

The results of the following figure, immediately see the effect is changed, welcome to discuss better, more accurate paging query.

The above T-SQL is only debugged on SQL Server 2005