When I used the new SQL Server2005 function to construct the paging storage process, I mentioned using the ROW_NUMBER () function to replace top to implement the paging storage process.
However, after a long time, we found a new problem, that is, paging query of the primary table and sub-table. For example, the Order table and order list must be an order query. On the second page, there are 10 orders on each page.
Copy codeThe Code is as follows:
-- 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.
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
The result is as follows:
Obviously, it is not 10 orders, but 10 details.
The following is an improvement on the query. The cursor is used.
Copy codeThe Code is as follows:
-- 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
As shown in the result, the effect changes immediately. You are welcome to discuss better and more precise paging queries.
The above T-SQL is successfully debugged only on SQL Server 2005.
We recommend that you use the ms SQL Server query plan to optimize SQL statements and write well. Reference: SQL Server execution plan and SQL query optimization