SQL Server database Row_number () function usage explained

SQL Server database Row_number () function usage explained

Excerpt from: http://database.51cto.com/art/201108/283399.htm

The use of the SQL Server database row_number () function is what we are going to introduce in this article, and then we will introduce the use of the Row_number () function through a few examples.

Examples are as follows:

1. Use the Row_number () function for numbering, as

Select Email,customerid, Row_number () over (order by PSD) as rows from Qt_customer

Principle: First sort by psd, after sorting, each piece of data is numbered.

2. Sort the orders in ascending order by price, and sort each record with the following code:

Select Did,customerid,totalprice,row_number () over (order by Totalprice) as rows from Op_order

3. Count all orders for each of the households and order them according to the amount of each customer's orders, and number each customer's order. This will know each customer next few orders.

The code is as follows:

Select Row_number () over (partition by CustomerID ORDER by Totalprice) as Rows,customerid,totalprice, do from Op_order

4. The most recent orders for each customer are orders placed under the first few times.

The code is as follows:

1. With tabs as
2. (
3. Select Row_number () over (partition by CustomerID ORDER by Totalprice) as Rows,customerid,totalprice, do from Op_order
4. )
5. Select MAX (rows) as ' Number of orders ', CustomerID from tabs group by CustomerID

5. Count the minimum amount purchased for each customer's order, and the number of times the customer purchased the order.

: Rows indicates that the customer is the first purchase.

Idea: Use temporal tables to perform this operation.

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

2. Then use a subquery to find out the minimum price for each customer purchase.

3. Find the appropriate record based on the minimum price found for each customer.

The code is as follows:

1. With tabs as
2. (
3. Select Row_number () over (partition by CustomerID ORDER by INSDT) as Rows,customerid,totalprice, do from Op_order
4. )
5. SELECT * FROM tabs
6. where Totalprice in
7. (
8. Select MIN (totalprice) from tabs GROUP by CustomerID
9. )

6. Filter out the orders placed by the customer for the first time.

Ideas. Use Rows=1 to check the customer's order record for the first time.

The code is as follows:

1. With tabs as
2. (
3. Select Row_number () over (partition by CustomerID ORDER by INSDT) as rows,* from Op_order
4. )
5. SELECT * from tabs where rows = 1
6. SELECT * FROM Op_order

7.rows_number () can be used for pagination

Idea: first to filter out all the products, and then the products are numbered. The filter is then filtered in the WHERE clause.

8. Note: When using the over window function, the grouping and sorting execution in over is later than "Where,group By,order by".

The following 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> ' 2011-07-22 '

SQL Server database Row_number () function usage explained