SQL Server: Several SQL statements that obtain the user's latest or previous N orders

Scenario: A User table and an order table are required to obtain the latest order information for a user.

To meet the above requirements, we can use the following methods, but the efficiency is quite different.

First, create an index in the order table:

Create   Unique   Index Idx_eid_odd_oidd On Orders (employeeid, orderdate Desc , Orderid Desc )

Multiple orderids are used in descending order of order numbers when orderdata is the same. They are secondary attributes.

Method 1:

Select Employeeid, orderid From Orders As O1
Where Orderid = (
Select   Top ( 1 ) Orderid From Orders As O2
Where O1.employeeid = O2.employeeid
Order   By Orderdate Desc , Orderid Desc
)

If you want to obtain the first N orders, change = to in, And then top (n.

Whether it is to retrieve one or more entries, even if there is an index and there is a large amount of data, it is also the slowest.

Method 2:

Select O. employeeid, O. orderid From (
Select Employeeid ,( Select   Top ( 1 ) Orderid From Orders As O2 Where E. employeeid = O2.employeeid Order   By Orderdate Desc , Orderid Desc ) As Orderid
From Employees As E
) As EO
Inner   Join Orders As O
On EO. orderid = O. orderid

Method 2 can take only one message, but not multiple information.

This is much faster than method 1 in the case of retrieving one entry, because the user information is much less than the order information.

Method 3:

Select E. employeeid, O. orderid From Employees As E
Cross Apply (
Select   Top ( 1 ) *   From Orders As O1 Where E. employeeid = O1.employeeid Order   By O1.orderdate Desc , O1.orderid Desc
) As O

This application is applied to some new features of SQL Server 2005 or later, which is more efficient than method 2.

If you want to obtain multiple entries, you only need to change top (n.

Apply details, see http://www.cnblogs.com/xbf321/archive/2011/08/14/apply-in-sql-server.html

Method 4:

Select O1.employeeid, o1.orderid
From Orders O1 Join (
Select Row_number () Over (Partition By Employeeid Order   By Orderdate Desc , Orderid Desc ) As Rownumber, *  
From Orders As OT
) As O2
On O1.orderid = O2.orderid
Where O2.rownumber =   1

This row_number function is also added after SQL Server 2005. This function does not have much to check with method 3, or even better than method 3. However, you must note that it is first partitioned by employeeid and then sorted.

 

We recommend that you use method 3 based on the above methods.