Data Group (count (*))
- Group by age to count the number of people of all ages:
Select Fage, count (*) from t_employee group by Fage
- The group by clause must be placed after the WHERE clause
- Columns not in the group by clause cannot be placed in the column name list after the SELECT statement (except in aggregate functions)
• Error:Select Fage, fsalary from t_employee group by Fage
• Correct:Select Fage, AVG (fsalary) from t_employee group by Fage
Having statement
- InAggregate functions cannot be used in where., Must be usedHaving, Having must be located after group,
Select Fage, count (*) as count from t_employee group by Fage having count (*)> 1
- Note that having cannot use columns that are not in the group, and having cannot replace where. Having filters groups.
Limit the number of rows in the result set (top)
- Select top 5 * From t_employee order by fsalary DESC
- (*) Retrieve the information of three people from the sixth place in descending order of salary:
Select top 3 * From t_employee
WhereFnumberNot in
(Select top5 fnumber from t_employee order by fsalary DESC)
Order by fsalary DESC
- The simplified implementation of the row_number function is added after sqlserver2005.
Remove data duplication (distinct)
- execute the SQL statement in the remarks, alter and insert is executed separately.
| select fdepartment from t_employee |
| select distinct fdepartment from t_employee |
- distinct repeat the data in the entire result set, instead of for each column, therefore, the following statement does not only retain fdepartment for repeated value processing:
select distinct fdepartment, fsubcompany
from t_employee
