Someone suggested that the cursor would be poor and the row would be locked. Fortunately, I locked a temporary table instead of a data table, which does not affect the write operation of the data table.
Below is the reply from the 14th floor, which made me feel like a plug-in. So with today's simplified version, I canceled the use of the cursor. The temporary table still exists. Thank you.
In fact, you only need to divide it into two queries:
1. Use Row_Number to query the page of the primary table.
2. Row_Number: query the table by PAGE inner join. The cursor is not used.
Copy codeThe Code is as follows:
-- Pagination without cursors
-- First place the paging primary table in the temporary table, and then use the temporary table and sub-table joint query to obtain the sub-table information
-- Not only ensures the correctness of paging, but also includes sub-table information
Create table # order
(
Number BIGINT,
Orderseqno VARCHAR (36 ),
)
Insert into # order
SELECT * FROM (SELECT ROW_NUMBER () OVER (order by oi. CreateDate DESC) AS rownumber, oi. OrderSeqNO
FROM OrderInfo oi WHERE oi. OrderSeqNO LIKE '% 100') AS o
WHERE o. rownumber BETWEEN 10 AND 20
SELECT * FROM # order inner join OrderDetail od ON od. OrderSeqNO = # order. orderseqno
Drop table # order
Copy codeThe Code is as follows:
-- Select top 10 oi. OrderSeqNO, oi. GoodsName, ci. CompanyName, od .*
-- FROM OrderInfo oi inner join CompanyInfo ci ON oi. CompanyID = ci. CompanyID
-- Left join OrderDetail od ON oi. OrderSeqNO = od. OrderSeqNO
-- Use row_unmber () to implement Paging
-- Originally we wanted 10 orders, but 10 details instead of 10 orders.
-- In fact, the subtable is paginated. The order is not the number to be displayed, and the number to be displayed is the number of details.
-- Because of the joint query results of the master table and sub-table, the relationship between the master table record and the sub-Table record is 1: N. a master table record has multiple details.
-- Create a clustered Index
-- Clustered index INDEX_OrderInfo ON OrderInfo (OrderSeqNo)
-- Display query execution plan
-- SET STATISTICS IO ON
Select * from
(SELECT ROW_NUMBER () OVER (order by oi. createdate DESC) AS rownumber, oi. orderseqno, od. OrderDetailID
FROM OrderInfo oi left join OrderDetail od ON oi. OrderSeqNO = od. OrderSeqNO
WHERE oi. OrderSeqNO LIKE '% 100'
) AS o
WHERE rownumber BETWEEN 10 AND 20
-- Pagination without cursors
-- First place the paging primary table in the temporary table, and then use the temporary table and sub-table joint query to obtain the sub-table information
-- Not only ensures the correctness of paging, but also includes sub-table information
Create table # order
(
Number BIGINT,
Orderseqno VARCHAR (36 ),
)
Insert into # order
SELECT * FROM (SELECT ROW_NUMBER () OVER (order by oi. CreateDate DESC) AS rownumber, oi. OrderSeqNO
FROM OrderInfo oi WHERE oi. OrderSeqNO LIKE '% 100') AS o
WHERE o. rownumber BETWEEN 10 AND 20
SELECT * FROM # order inner join OrderDetail od ON od. OrderSeqNO = # order. orderseqno
Drop table # order
-- There are several ways to solve the above problems:
-- 1. First query the records of the master table based on the conditions, and then loop through the C # code. Then, query the details of each record of the master table in the database, and assign the values to the attributes.
-- 2. When using a cursor in the database storage process, you also need to first query the records of the master table, and then query the sub-table information in the process of using a cursor loop, and then in C #
-- Centralized processing
-- Obviously, the latter method reduces the database's transaction overhead and obtains the desired data at a time. I personally think it is better than the first one. You are welcome to discuss a better solution.
-- Note that ROW_NUMBER () returns a bigint type instead of an int type.
-- The following is the cursor storage process.
-- Create a temporary table for the master table
Create table # temp
(
Rownumber bigint,
Orderseqno VARCHAR (36 ),
Goodsname VARCHAR (50 ),
Companyname VARCHAR (0, 100)
)
-- Create a temporary sub-table
Create table # detail
(
Orderseqno VARCHAR (36 ),
Detailid UNIQUEIDENTIFIER,
Unitprice DECIMAL (12, 2 ),
Qty int
)
-- Insert data from the primary table to the temporary table of the primary 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 10 AND 20
-- Define a cursor
DECLARE @ temp_cursor CURSOR
-- Assign a value to the cursor
SET @ temp_cursor = cursor for select # temp. orderseqno, # temp. goodsname FROM # temp
-- Defines the temporary data to be saved during the cursor Loop
DECLARE @ orderseqno VARCHAR (36), @ goodsname varchar (50)
-- Open the cursor
OPEN @ temp_cursor
Fetch next from @ temp_cursor INTO @ orderseqno, @ goodsname
-- Cyclically cursors: queries data in the sub-table and inserts a temporary table into the sub-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 the cursor
CLOSE @ temp_cursor
DEALLOCATE @ temp_cursor
SELECT * FROM # temp
SELECT * FROM # detail
-- Delete a temporary table
Drop table # temp
Drop table # detail