SQLServer学习笔记九：DISTINCT子句和子查询

学习目标

• 使用DISTINCT消除重复，仅提取唯一记录
• 使用子查询返回数据，作为进一步限制要检索的条件

DISTINCT

DISTINCT关键字与SELECT语句一起使用，用来消除重复，并仅提取唯一记录。
当记录集中有多个重复记录时，更有意义的是仅提取唯一记录，而不是看到重复数据，此时可以使用DISTINCT进行处理。

语法

以下是DISTINCT关键字的基本语法，用于删除重复记录。

SELECT DISTINCT 列1, 列2,.....  
FROM 表名
WHERE 条件 

例如，查询学生表中一共出现了哪些民族的同学，对应T-SQL如下：

SELECT DISTINCT nation
FROM students

查询结果

注意：DISTINCT不会过滤掉NULL值，返回结果包含NULL值

子查询

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。即如果需要在一个查询结果集基础上进行增删改，或者进一步的查询就需要用到子查询。

子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。

• 外部查询：查询结果集返回给调用者
• 内部查询：查询结果集返回给外部查询。

独立子查询

独立子查询（不相关子查询）是指独立于其外部查询的子查询，可以单独运行子查询。

执行顺序

独立子查询在执行外部查询之前先执行一次，接着外部查询再使用子查询的结果继续进行查询。

独立标量子查询

独立标量子查询是返回单个值的子查询。标量子查询可以出现在外部查询中期望使用单个值的任何地方。例如，查询教师最多的院系。

查询过程可以分为两步：

• 从teachers表中返回教师人数最多的院系编号

SELECT TOP 1 deptno
FROM teachers
GROUP BY deptno
ORDER BY COUNT(*) DESC

返回结果

• 从departments表示返回该编号的院系名称

SELECT deptno, dname
FROM departments
WHERE deptno = 'J' 

返回结果

更简单的方法是嵌套子查询，只需要一条查询语句就可以查询出教师最多的院系。

SELECT deptno, dname
FROM departments
WHERE deptno = (
	SELECT TOP 1 deptno
	FROM teachers
	GROUP BY deptno 
)

返回结果

注意

• 对于有效的标量子查询，它的返回值不能超过一个，如果标量子查询返回了多个值，在运行时则可能会失败。
• 如果标量子查询没有返回任何值，其结果就转换为NULL，和NULL行进行比较得到的是UNKNOWN，查询过滤器不会返回任何让过滤表达式计算结果为UNKNOWN的行。

独立多值子查询

独立多值子查询是在子查询中返回多个值。例如，查询所有男生（或女生）的平均分。

SELECT sno, AVG(grade) AS 平均分 
FROM score
WHERE sno in (
	SELECT sno
	FROM students
	WHERE gender='男'
)
GROUP BY sno

返回结果

相关子查询

相关子查询（关联子查询）是指子查询中引用了外部查询中出现的表的列，依赖于外部查询，不能独立地运行子查询。在逻辑上，子查询会为每个外部行单独计算一次。

练习1

在students表中添加一列point，记录每个同学的学分，学分等于该生各科的平均分。实现过程可以分为两步：

• 给学生表添加point列

ALTER TABLE students
ADD points INT

• 更新students表的point列，将其设置为该同学各科的平均分。

UPDATE students SET points = (
	SELECT AVG(grade)
	FROM score AS SC
	WHERE SC.sno = students.sno
) 

练习2

修改score表中的成绩，然后查询students表中哪些同学的学分points发生了变化。

SELECT sno, sname
FROM students AS S
WHERE S.points <> (
	SELECT AVG(grade)
	FROM score AS SC
	WHERE SC.sno = S.sno
)

注意，在查询过程中要先外后内，外层查询决定最终要显示的表和字段，然后由内层查询确定查询条件。

EXISTS和NOT EXISTS

EXISTS（或NOT EXISTS）可以指定一个子查询，用于检查子查询是否至少会返回一行数据。该子查询通过EXISTS（或NOT EXISTS）并不返回任何数据，而是返回True或False。

语法

EXISTS ( 子查询)

练习

使用EXISTS查询不及格的学生信息，包含学生编号，学生姓名。

  SELECT sno, sname
  FROM students AS S 
  WHERE EXISTS (
	SELECT DISTINCT sno
	FROM score AS SC
	WHERE SC.sno = S.sno AND SC.grade < 60
  )

执行顺序

1. 首先执行一次外部查询
2. 对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中的列

查询结果

注意：

• 如果子查询查询结果有多条数据只要查询出一条记录后，就会立即返回，这种处理方式叫做短路处理。
• NOT EXISTS子查询的返回结果刚好相反。

总结

• DISTINCT关键字与SELECT语句一起使用，用来消除重复，并仅提取唯一记录。
• 子查询分为不相关子查询和相关子查询
• 不相关子查询是指子查询独立于其外部查询，可以单独运行子查询。
• 相关子查询是指子查询中引用了外部查询中出现的表的列，依赖于外部查询，不能独立地运行子查询。
• EXITS可以指定一个子查询，用于检查子查询是否至少会返回一行数据。