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.
CopyCode The 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 code The Code is as follows: -- there are several ways to solve the problem:
-- 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 T-SQL above is successfully debugged only on SQL Server 2005.
it is recommended 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
