The SQL Server2005 database comes with a sort function row_number (),
The list is:
Syntax: Row_number (): Over (clause)
1. Use the Row_number () function to number:
Select Row_number () over (order by Sage) ID, * from Student
Principle: Sort by sage first, and then number each piece of data.
Note: When using the over window function, the grouping and sorting execution in over is later than "Where,group By,order by".
The result is:
2. Use the Row_number () function for statistics:
Select Row_number () over (partition by Ssex ORDER by Sage) as rows,* from Student
Note:partition by is the data partition fetch number, used to group the result set, if not specified then it takes the whole result set as a group;
It differs from the aggregation function in that it can return multiple records in a group, whereas aggregate functions generally have only one record that reflects the statistical value.
Principle: Count the number of students of different sexes and sort them according to the age of each student, and give each student a number. This will know the number of boys and girls.
Results:
SQL Server sort function