Grammar:
Row_number () over ([<partition_by_clause>] <order_by_clause>)
Note:
The ORDER BY clause determines the sequence in which the rows are assigned unique row_number in a particular partition.
Parameters:
<partition_by_clause> The result set generated from clause is drawn into the partition where the Row_number function is applied.
<order_by_clause> determines the order in which row_number values are assigned to rows in the partition. For more information, see the ORDER BY clause (Transact-SQL).
return type:
bigint
Examples of limit functional syntax similar to MySQL:
Copy Code code as follows:
SELECT * FROM (select Row_number () over (order by ID ASC) as rownum, * from MyTable) as items WHERE Items.rownum BETWEEN 20 and 30;
SQL Row_number () sort function
1 using the Row_number () function for numbering:
Copy Code code as follows:
Select Email,customerid, Row_number () over (order by PSD) as rows from Qt_customer
Principle: First, according to the PSD to sort, after sorting, the number of each piece of data.
2. Sort in order by price ascending, and sort each record
The code is as follows:
Copy Code code as follows:
Select Did,customerid,totalprice,row_number () over (totalprice) as rows from Op_order
3. All orders of each household are counted and sorted in ascending order by each customer, and the order for each customer is numbered. So you know the next few orders for each customer.
The code is as follows:
Copy Code code as follows:
Select Row_number () over (partition by CustomerID ORDER by Totalprice) as Rows,customerid,totalprice, DID from Op_order
4. The most recent orders for each customer are the orders placed on the first few occasions.
The code is as follows:
Copy Code code as follows:
With tabs as
(
Select Row_number () over (partition by CustomerID ORDER by Totalprice) as Rows,customerid,totalprice, DID from Op_order
)
Select MAX (rows) as ' next single times ', CustomerID from tabs group by CustomerID
5. Statistics of each customer's purchase of all orders of the smallest amount, and the change in the order, the customer is the first several times purchased.
As shown in figure:
Above: rows indicates how many times the customer purchased the first.
Idea: Use temporary tables to perform this action
1. Group by customer first, and then sort by customer's order time, and number.
2. Then use the subquery to find out the minimum price per customer when buying.
3. Find the appropriate records according to the minimum price for each customer.
The code is as follows:
Copy Code code as follows:
With tabs as
(
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 out the first orders from customers.
Ideas. Use Rows=1 to query the customer's first order record.
The code is as follows:
Copy Code code as follows:
With tabs as
(
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 sift through all the products and then number the products. Then filter in the WHERE clause.
7. Note: In the use of over and so open window functions, over inside the grouping and sorting execution later than "Where,group by,order by" implementation.
The following code:
Copy Code code as follows:
Select
Row_number () over (partition by CustomerID ORDER by INSDT) as rows,
Customerid,totalprice, DID
From Op_order where insdt> ' 2011-07-22 '
The above code executes the WHERE clause first, and then the number of each record is executed.
