SqlServer series notes-query filtering, grouping and sorting

DISTINCT

DISTINCT duplicates data for the entire result set, not for each column

Therefore, the following statement does not leave only Fdepartment for duplicate value processing:

SELECT DISTINCT FDepartment,FSubCompany

FROM T_Employee

2. GROUP BY

Count the number of people in each age group according to age:

SELECT FAge,Count (*) FROM T_Employee GROUP BY Fage

The GROUP BY clause must be placed after the WHERE statement

Columns that do not appear in the GROUP BY clause cannot be placed in the list of column names after the select statement (except in the aggregate function)

Error:

SELECT FAge,FSalary FROM T_Employee GROUP BY FAge

Correct:

SELECT FAge,AVG (FSalary) FROM T_Employee GROUP BY FAge

3.Having

Aggregate functions cannot be used in Where, and Having,Having must be used after Group By:

Number of SELECT FAge,COUNT (*) AS FROM T_Employee

GROUP BY FAge

HAVING COUNT (*) > 1

Note that columns that are not grouped cannot be used in Having, and Having cannot replace where. The function is different, Having is to filter the group.

4.In

Delete T_Employee where FId in (21 and 22)

SELECT FAge,FNumber,FName FROM T_Employee

WHERE FAge IN (23, 25, 28)

Range value:

SELECT * FROM T_Employee WHERE FAGE > = 23 AND FAGE 23 ORDER BY FAge DESC,FSalary DESC