SQL statement about row_number () over ()

For SQL statements about row_number () over (), today we use row_number () over (). Now we will summarize it in the form of knowledge points for future review. ------------------- Reference MSDN ------------------- Syntax: ROW_NUMBER () OVER ([PARTITIONBYvalue_expression,... [n] order _

For SQL statements about row_number () over (), today we use row_number () over (). Now we will summarize it in the form of knowledge points for future review. ------------------- Reference MSDN ------------------- Syntax: ROW_NUMBER () OVER ([partition by value_expression,... [n] order _

SQL statement about row_number () over ()

Today, row_number () over () is used to summarize the website space in the form of knowledge points for future review.

------------------- Reference MSDN -------------------

Syntax:

ROW_NUMBER () OVER ([partition by value_expression,... [n] order_by_clause)

The syntax shows that there are two parameters in over: partition by, order by, and website space. In this example, partition by can be left blank, but order by is required. I may be familiar with order by (which is sort by), but what does partition by mean? The Chinese interpretation of partition is: n. Division, separation; [number] segmentation; partition wall; isolation vt. [number] segmentation; separation; differentiation. Let's combine the following parameter description and the instance to easily understand its meaning.

Parameters:

Return Value Type:

Bigint (long integer)

The above is a theoretical understanding of row_number () over (). Now we will use an example to demonstrate it:

Create a table first (dbo. leleinfo ):

. () Not null, (10) COLLATE Chinese_PRC_CI_AS NULL, (10) COLLATE returns NULL, (10) COLLATE Chinese_PRC_CI_AS NULL, (10) COLLATE Chinese_PRC_CI_AS NULL ,)

Insert data into the table:

, Gender, numb, phone, fenshu), 80), Gender, numb, phone, fenshu), 90), Gender, numb, phone, fenshu), 56), Gender, numb, phone, fenshu), 60), Gender, numb, phone, fenshu), 80)

Query all inserted data:

Dbo. leleinfo

Result

Example 1: the SQL statement that only uses order by without partition by is as follows:

, Gender, fenshu, row_number () over (order by fenshu desc) as num from dbo. PeopleInfo

Result

Example 2: the SQL statement that uses order by and partition by is as follows:

, Gender, fenshu, row_number () over (partition by Gender order by fenshu desc) as num from dbo. PeopleInfo

Result

Comparing the results of examples 1 and 2, we can easily understand the usefulness of partition by. in example 2, we use partition by to divide Gender into two areas, one male and one female, the Hong Kong VM then uses order by to sort the [fenshu] scores in each partition from large to small.

-----------------------------------------------

Exercise questions (use the cte (... As) knowledge points can be viewed ):

(, Gender, fenshu, row_number () over (partition by Gender order by fenshu desc) as num from dbo. PeopleInfo) num = 1

Result