Create table OverTableTest
(
Id int identity (1, 1), -- ID
Val int, -- Value
Typ int, -- type
)
Go
Declare @ cou int
Set @ cou = 1
While @ cou <50
Begin
Insert into OverTableTest
Select @ cou, @ cou % 5 + 1
Set @ cou = @ cou + 1
End
Go
Select * from OverTableTest
/*
Usually we will use the following scenario: Paging (row_Number)
Example:
*/
; With mycte
(
Select row = ROW_NUMBER () over (order by id desc), * from
OverTableTest
) Select * from mycte where row BETWEEN 10 and 20
/*
In the above cases, I often use,
However, I have never asked the question about the use of Over.
*/
/*
Application environment 2: obtain the first few in a group
It's like, after the exam, getting the top 3 students in each subject
SQL:
*/
; With mycte
(
Select row = ROW_NUMBER () over (partition by typ order by val desc), * from
OverTableTest
) Select * from mycte where row <= 3
/*
In this example, partition by is used for grouping statistics.
Let's take the third example:
Obtain the number of categories by category, and sort by the maximum number of categories
*/
Select Cunt = count (0) over (partition by typ), * from
OverTableTest order by Cunt desc, typ, id
-----------------------------------------------------------------
/*
This is the example for today. You can copy and execute the running results directly.
*/
Transferred by others