### T-SQL高级查询知识点详解
#### 一、SELECT语句在单表查询中的应用（分组、统计函数等）
**1. 分组 (GROUP BY)**
`GROUP BY` 子句用于结合聚合函数（如 COUNT(), AVG(), MAX(), MIN(), SUM()），根据一个或多个列对结果集进行分组。
- **示例：查询数据库中各班级的平均成绩。**
```sql
SELECT class_id, AVG(grade) AS AverageGrade
FROM result
WHERE student_id IN (
SELECT student_id
FROM student
WHERE class_id IN (
SELECT class_id
FROM class
)
)
GROUP BY class_id;
```
- **解析：**
- 首先通过子查询获取所有学生所在的班级ID。
- 使用 `GROUP BY class_id` 对结果按班级进行分组。
- 使用 `AVG(grade)` 计算每个班级的平均成绩。
**2. 统计函数**
- **示例：查询程序设计这门课的最高分、最低分及其之间的分差。**
```sql
SELECT MAX(grade) AS MaxGrade, MIN(grade) AS MinGrade, (MAX(grade) - MIN(grade)) AS GradeDifference
FROM result
WHERE course_id IN (
SELECT course_id
FROM course
WHERE name = '程序设计'
);
```
- **解析：**
- 通过子查询确定课程ID。
- 使用 `MAX(grade)` 和 `MIN(grade)` 计算最高分和最低分。
- 计算两者之间的差异。
**3. 条件过滤 (HAVING)**
`HAVING` 子句用于过滤聚合函数的结果，它与 `WHERE` 子句类似，但 `HAVING` 只能用于聚合后的数据。
- **示例：查询总分大于350分的学生的学号及总成绩。**
```sql
SELECT student_id, SUM(grade) AS TotalGrade
FROM result
GROUP BY student_id
HAVING SUM(grade) > 350;
```
- **解析：**
- 使用 `SUM(grade)` 对成绩求和。
- 使用 `GROUP BY student_id` 按学生ID分组。
- 使用 `HAVING SUM(grade) > 350` 过滤出总分大于350分的学生。
**4. DISTINCT关键字**
`DISTINCT` 关键字用于返回唯一的不同的值。
- **示例：统计学生来自多少个城市。**
```sql
SELECT COUNT(DISTINCT LEFT(home_addr, 3)) AS NumberOfCities
FROM student;
```
- **解析：**
- 使用 `LEFT(home_addr, 3)` 获取城市前三位字符作为城市标识。
- 使用 `COUNT(DISTINCT ...)` 计算不同城市的数量。
#### 二、基于多表的连接查询
**1. JOIN操作**
JOIN 是 SQL 中用来组合两个或更多表的行的一种方法。
- **示例：查询每个教师的教师号及其任课门数。**
```sql
SELECT teacher.teacher_id, COUNT(DISTINCT teaching.course_id) AS NumberOfCourses
FROM teaching
JOIN teacher ON teaching.teacher_id = teacher.teacher_id
GROUP BY teacher.teacher_id;
```
- **解析：**
- 使用 `JOIN` 将 `teaching` 表和 `teacher` 表通过教师ID关联起来。
- 使用 `COUNT(DISTINCT teaching.course_id)` 计算每位教师教授的不同课程的数量。
- 使用 `GROUP BY teacher.teacher_id` 按教师ID分组。
**2. 多表连接**
可以同时连接三个或更多表来获取所需的数据。
- **示例：查询选修三门以上课程的学生学号和选课门数。**
```sql
SELECT student_id, COUNT(DISTINCT course_id) AS NumberOfCourses
FROM result
GROUP BY student_id
HAVING COUNT(DISTINCT course_id) > 3;
```
- **解析：**
- 使用 `GROUP BY student_id` 按学生ID分组。
- 使用 `COUNT(DISTINCT course_id)` 计算每位学生选修的不同课程的数量。
- 使用 `HAVING COUNT(DISTINCT course_id) > 3` 过滤出选修课程超过三门的学生。
#### 三、嵌套子查询
**1. 内外查询**
嵌套子查询是指在一个查询语句中包含另一个查询语句。
- **示例：统计男、女学生各自的总人数和平均年龄。**
```sql
SELECT sex, COUNT(student_id) AS NumberOfStudents, AVG(YEAR(GETDATE()) - YEAR(birthday)) AS AverageAge
FROM student
GROUP BY sex;
```
- **解析：**
- 使用 `GROUP BY sex` 按性别分组。
- 使用 `COUNT(student_id)` 计算每个性别的人数。
- 使用 `AVG(YEAR(GETDATE()) - YEAR(birthday))` 计算平均年龄。
- **示例：查询刘老师所讲授的课程名称。**
```sql
SELECT course.name
FROM course
WHERE course_id IN (
SELECT course_id
FROM teaching
WHERE teacher_id IN (
SELECT teacher_id
FROM teacher
WHERE teacher.name LIKE '刘%'
)
);
```
- **解析：**
- 内层子查询首先找到所有姓“刘”的教师ID。
- 中间层子查询根据教师ID获取其教授的所有课程ID。
- 外层查询根据课程ID获取课程名称。
**2. 子查询作为条件**
子查询也可以用作 `WHERE` 子句中的条件。
- **示例：查询比杨勇年龄大的学生基本信息。**
```sql
SELECT *
FROM student
WHERE birthday < (
SELECT birthday
FROM student
WHERE name = '杨勇'
);
```
- **解析：**
- 内层子查询获取名为“杨勇”的学生的生日日期。
- 外层查询返回所有比杨勇年龄大的学生的全部信息。
**总结：**
本文详细介绍了 T-SQL 中的高级查询技巧，包括单表查询中的分组、统计函数等操作，以及多表连接查询和嵌套子查询的应用。这些技能是掌握 SQL 数据库管理的基础，并在实际工作中有着广泛的应用场景。通过这些示例的学习和实践，可以帮助读者更好地理解和运用这些技术。
