Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use Partition By and row_number functions in SQLServer. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

First of all, I put the data in the table in reverse order according to the submission time:

"corp_name" is the GUID of the classification (please forgive the arbitrariness of my naming). OK, let's take a look at the display with the original idea and Group By:

Uh, uh-huh. This Nima is different from the imagined result, it seems that writing code still needs rational analysis of the problem, the idea is unable to control the result!

Since the requirements are different categories of data, is there any other function that can be used besides using Group By? The result is really over (partition by) function, so what's the difference between it and the usual Group By? In addition to simply grouping the results, Group By is generally used with the aggregate function. Partition By also has a grouping function, which belongs to the analysis function of Oracle, so it won't be detailed here.

Look at the code:

Over (partition by corp_name order by submit_time desc) as t. It is sorted by corp_name and sorted in reverse order in time. The column "t" here is the number of times that different corp_name classes appear. The requirement is to query only the latest submitted data of different categories, so we only need to filter for "t" again:

Well, the result has come out. I don't ask you to like it, but I ask you to look at the chest in my avatar and like it. A good man is safe all his life!

Detailed explanation of the use of partition by and ROW_NUMBER () function in ps:SQL Server Database

Some experiences on the usage of the partition by field of SQL

Let's look at the example first:

If object_id ('TESTDB') is not null drop table TESTDBcreate table TESTDB (A varchar (8), B varchar (8)) insert into TESTDBselect' A1,'B1 'union allselect' A1,'B2 'union allselect' A1,'B3 'union allselect' A2,'B4 'union allselect' A2,'B5 'union allselect' A2,'B6 'union allselect' A3,'B7 'union allselect' A3,'B3 'union allselect' A3,'B4'

-- all the information

SELECT * FROM TESTDBA B-A1 B1A1 B2A1 B3A2 B4A2 B5A2 B6A3 B7A3 B3A3 B4

After using the PARTITION BY function

SELECT *, ROW_NUMBER () OVER (PARTITION BY An ORDER BY A DESC) NUM FROM TESTDBA B NUM-A1 B1 1A1 B2 2A1 B3 3A2 B4 1A2 B5 2A2 B6 3A3 B7 1A3 B3 2A3 B4

You can see that there is an extra column of NUM in the result. The NUM indicates the number of peers. For example, if there are three A1, he marks the number of each A1.

-- results of using only ROW_NUMBER () OVER

SELECT *, ROW_NUMBER () OVER (ORDER BY A DESC) NUM FROM TESTDB A B NUM----A3 B7 1A3 B3 2A3 B4 3A2 B4 4A2 B5 5A2 B6 6A1 B1 7A1 B2 8A1 B3 9

You can see that it simply marks the line number.

-- go deeper into the application.

SELECT A = CASE WHEN NUM = 1 THEN An ELSE''END,BFROM (SELECT A num = ROW_NUMBER () OVER (PARTITION BY An ORDER BY A DESC) FROM TESTDB) TA B-A1 B1 B2 B3A2 B4 B6A3 B7 B3 B4

Next, we will introduce the use of the ROW_NUMBER () function one by one through several examples.

Examples are as follows:

1. Use the row_number () function for numbering, such as

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 after sorting.

two。 Sort the order in ascending order of price, and sort each record as follows:

Select DID,customerID,totalPrice,ROW_NUMBER () over (order by totalPrice) as rows from OP_Order

3. Count all orders for each household and sort them in ascending order according to the amount of orders placed by each customer, and number each customer's order at the same time. In this way, we will know how many orders each customer places.

As shown in the figure:

The code is as follows:

Select ROW_NUMBER () over (partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order

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

The code is 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' orders placed', customerID from tabs group by customerID

5. Statistics of each customer all the orders of the purchase amount is the smallest, and statistics of the change order, the customer is the number of purchases.

As shown in the figure:

Image above: rows indicates the number of purchases made by the customer.

Idea: use temporary tables to do this.

1. It is grouped by customer, and then sorted and numbered by the time the customer placed the order.

two。 Then use the subquery to find out the minimum price of each customer at the time of purchase.

3. According to find out the minimum price of each customer to find the corresponding records.

The code is 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)

6. Filter out the first order placed by the customer.

Train of thought. Use rows=1 to query the record of the first order placed by the customer.

The code is 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

7.rows_number () can be used for paging

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

8. Note: when using windowing functions such as over, the execution of grouping and sorting in over is later than that of "where,group by,order by".

The code is 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' this is how to use Partition By and row_number functions in SQLServer shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.