To achieve the above requirements, we can use the following several ways, but the efficiency is far from the difference.
First we create an index in the order table:
CREATE UNIQUE INDEX Idx_eid_odd_oidd on Orders (employeeid,orderdate Desc,orderid DESC)
Multiple OrderID is a secondary attribute that is in the same case as orderdata, in reverse order number.
Method 1:
Copy Code code as follows:
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 get the first n order information, change the = number to In, and then Top (n).
Whether to take one or more, even if there is an index, the data is more than the case, but also the slowest.
Method 2:
Copy Code code as follows:
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 only take one piece of information, can not take more than one message.
In the case of taking one, this is much faster than Method 1, because the user is much less than the order information.
Method 3:
Copy Code code as follows:
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 applies to some of the new features of SQL Server 2005 or later, which is more efficient than Method 2.
If you want to get more than one, just change Top (n).
Apply detailed, see http://www.jb51.net/article/28105.htm
Method 4:
Copy Code code as follows:
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, using this and Method 3 not much, or even better than 3, but note that you first press EmployeeID partition, and then sort.
In combination with the above methods, the proposed method is 3.
