How does SQLServer get the latest or first n orders from users?

This article introduces the knowledge of "how to get the latest or first n orders from SQLServer". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, we create an index in the Order table:

CREATEUNIQUEINDEXidx_eid_odD_oidDONOrders (EmployeeID,OrderDateDESC,OrderIDDESC)

Multiple OrderId is intended to be an auxiliary attribute in reverse order by order number in the case of the same OrderData.

Method 1:

SELECTEmployeeID,OrderIDFROMOrdersASO1WHEREOrderID= (SELECTTOP 1) OrderIDFROMOrdersASO2WHEREO1.EmployeeID=O2.EmployeeIDORDERBYOrderDateDESC,OrderIDDESC)

If you want to get the first n order information, change the = sign to IN, and then TOP (n).

Whether it is to take one or more, even if there is an index, there is a lot of data, it is also the slowest.

Method 2:

SELECTO.EmployeeID,O.OrderIDFROM (SELECTEmployeeID, (SELECTTOP (1) OrderIDFROMOrdersASO2WHEREE.EmployeeID=O2.EmployeeIDORDERBYOrderDateDESC,OrderIDDESC) ASOrderIDFROMEmployeesASE) ASEOINNERJOINOrdersASOONEO.OrderID=O.OrderID

Method 2 can only take one piece of information, not multiple pieces of information.

In the case of taking one, this is much faster than method 1, because the user has much less order information.

What are the SQL statements for SQLServer to get the user's latest or previous n orders?

Method 3:

SELECTE.EmployeeID,O.OrderIDFROMEmployeesASECROSSAPPLY (SELECTTOP (1) * FROMOrdersASO1WHEREE.EmployeeID=O1.EmployeeIDORDERBYO1.OrderDateDESC,O1.OrderIDDESC) ASO

This applies to some of the new features of SQLServer2005 or later, which is more efficient than method 2.

If you want to get more than one, just change TOP (n).

Method 4:

SELECTO1.EmployeeID,O1.OrderIDFROMOrdersO1JOIN (SELECTROW_NUMBER () OVER (PARTITIONBYEmployeeIDORDERBYOrderDateDESC,OrderIDDESC) ASRowNumber,*FROMOrdersASOT) ASO2ONO1.OrderID=O2.OrderIDWHEREO2.RowNumber=1

This ROW_NUMBER function is also added after SQLServer2005, using this and method 3 is not much, or even better than 3, but it is important to note that you first press the EmployeeID partition, and then sort it.

Combined with the above methods, it is suggested to use method 3.

This is the end of the content of "how to get the latest or first n orders from SQLServer". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!