SQL Server does not sort when ROW_NUMBER is used

-- 1. We can see that nhib.pdf is a page written in this way, and it feels easier to write (should it be less efficient ?)
-- With is just an alias?

[SQL]

The code is as follows:	Copy code
With query as (select ROW_NUMBER () over (order by (select 0) as rownum, * FROM Product) Select * from query where rownum between 5 AND 10

-- 2. ROW_NUMBER must specify the write over (order by **). Sometimes I don't want to sort it at all and want to sort it in the original order (it also takes time to sort it)

-- The method is:

The code is as follows:	Copy code
Select ROW_NUMBER () over (order by (select 0) as rownum, * FROM Product

Sorting is:

The code is as follows:	Copy code
Select Row_number () over (order by Oper_Date desc) as rownum, * FROM Product

Next, let's look at an instance ROW_NUMBER sorting instance.

1 Use the row_number () function for serial number: as shown in figure

The code is as follows:	Copy code
Select email, customerID, ROW_NUMBER () over (order by psd) as rows from QT_Customer

Principle: first sort by psd, and then number each piece of data.

2. Sort the order by Price in ascending order and sort each record

The code is as follows:

The code is as follows:	Copy code
Select DID, customerID, totalPrice, ROW_NUMBER () over (order by totalPrice) as rows from OP_Order

3. All orders of each household are collected and sorted in ascending order based on the order amount of each customer. Orders of each customer are numbered. In this way, you will know how many orders each customer has.

As shown in the following figure:

 

The code is as follows:

The code is as follows:	Copy code
Select ROW_NUMBER () over (partition by customerID order by totalPrice) as rows, customerID, totalPrice, DID from OP_Order

4. Count the number of orders placed by each customer recently.

 

The code is as follows:

The code is as follows:	Copy code
1 with tabs 2 ( 3 select ROW_NUMBER () over (partition by customerID order by totalPrice) as rows, customerID, totalPrice, DID from OP_Order 4) 5 6 select MAX (rows) as 'order times', customerID from tabs group by customerID

5. Count the minimum amount purchased in all orders of each customer, and count the number of times the customer purchased the order.

As shown in the following figure:

 

As shown in the preceding figure, rows indicates the number of times the customer made the purchase.

Idea: use a temporary table to perform this operation

1. Group by customer first, and then sort by customer's order time and number.

2. Then, use the subquery to find the minimum price for each customer's purchase.

3. Find the corresponding record based on the minimum price of each customer.

 

The code is as follows:	Copy code
With tabs ( Select ROW_NUMBER () over (partition by customerID order by insDT) as rows, customerID, totalPrice, DID from OP_Order ) Select * from tabs Where totalPrice in            ( Select MIN (totalPrice) from tabs group by customerID            )

 

5. Filter the orders placed by the customer for the first time.

 

Train of Thought. Use rows = 1 to query the order record of the customer for the first time.
 

With tabs
(
Select ROW_NUMBER () over (partition by customerID order by insDT) as rows, * from OP_Order
)
Select * from tabs where rows = 1
 
Select * from OP_Order

6. rows_number () can be used for paging

 

Idea: first filter out all products and then number these products. Then, filter in the where clause.

 

7. Note: when using over and other window functions, the execution of grouping and sorting in over is later than that of "where, group by, order.

The following code:

The code is as follows:	Copy code
1 select 2 ROW_NUMBER () over (partition by customerID order by insDT) as rows, 3 customerID, totalPrice, DID 4 from OP_Order where insDT> '2017-07-22'

The above code executes the where clause first, and then numbers each record after execution.