SQL Server温故系列(3)：SQL 子查询 & 公用表表达式 CTE

• 1、子查询 subqueries
• 2、公用表表达式 cte

1、子查询 subqueries

子查询是一个嵌套在 select、insert、update 或 delete 语句或其他子查询中的查询。通俗来讲，子查询就是嵌套在大“查询”中的小查询。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。

从概念上说，子查询结果会代入外部查询（尽管这不一定是 sql server 实际处理带有子查询的 t-sql 语句的方式）。所以子查询会在其父查询之前执行，以便可以将内部查询的结果传递给外部查询。

比较常见的子查询有：单行子查询、多行子查询、相关子查询、嵌套子查询等。然而并没有一种泾渭分明的子查询分类方法，换句话说，有可能某个子查询既是多行子查询，也是相关子查询，同时还是嵌套子查询。

1.1、单行子查询

顾名思义，单行子查询就是只查询一行数据的内部查询。如果单行子查询仅返回单一值，就可以称之为标量子查询。标量子查询也是最常见的单行子查询。示例如下：

-- 查询年龄最小的学生
select * from t_students where birthday = (select max(birthday) from t_students);

-- 第 1 次课程 1 考试的成绩高于学生 12 的成绩
select studentid,scores from t_examresults 
where counts = 1 and courseid = 1 and scores > (
    select scores from t_examresults where counts = 1 and courseid = 1 and studentid = 12);

-- 历次课程 1 考试的平均分高于学生 12 的成绩
select studentid,avg(scores) avgscore,count(1) examcount from t_examresults 
where courseid = 1 
group by studentid 
having avg(scores) > (select avg(scores) from t_examresults where courseid = 1 and studentid = 12);

1.2、多行子查询

相较于单行子查询，多行子查询就是会返回多行的内部查询。示例如下：

-- 查询有女生的班级里的学生
select * from t_students where classid in(select classid from t_students where gender = 0);

-- 查询有女生的班级之外的所有班级的学生
select * from t_students where classid not in(select classid from t_students where gender = 0);

-- 查询有 2003 年及以后出生的学生的班级
select * from t_classes where id in(select classid from t_students where birthday >= '2003-01-01');

1.3、相关子查询

相关子查询是指查询条件引用了外部查询中字段的内部查询。相反的，如果外部查询的字段没有出现在内部查询的条件中即为非相关子查询。相关子查询的内部查询得依靠外部查询获得值，这意味着内部查询是重复执行的，为外部查询选择的每一行都要执行一次，因此相关子查询也被称之为重复子查询。示例如下：

-- 查询在三(1)班和三(2)班的学生
select * from t_students t1 where exists(
    select id from t_classes t2 where t2.id = t1.classid and t2.name in('三(1)班','三(2)班'));

-- 查询不在三(1)班和三(2)班的学生
select * from t_students t1 where not exists(
    select id from t_classes t2 where t2.id = t1.classid and t2.name in('三(1)班','三(2)班'));

-- 查询第 1 次考试的课程及参加了的学生
select (select t2.name from t_courses t2 where t2.id=t1.courseid) coursename,
       (select t3.name from t_students t3 where t3.id=t1.studentid) studentname 
from t_examresults t1 where t1.counts = 1;

1.4、嵌套子查询

嵌套子查询是指查询内部嵌套一个或多个子查询的内部查询。一个 t-sql 语句中可以嵌套任意数量的子查询，尽管通常来说没有这种必要。示例如下：

-- 查询参加了第 1 次课程 1 考试的学生
select * from t_students t3 where t3.id in(
    select t2.studentid from t_examresults t2 where t2.counts = 1 and t2.courseid = (
        select t1.id from t_courses t1 where t1.name = '英语'));

-- 查询西湖区所在的城市的所有学生
select t3.* from t_students t3 where substring(t3.code,2,6) in(
    select t2.code from t_districts t2 where t2.parentid = (
        select t1.parentid from t_districts t1 where t1.name = '西湖区'));

1.5、子查询小结及性能问题

上文主要讲述了查询语句中的子查询，其实在增删改语句中也一样能够使用子查询。任何能使用表达式的地方都可以使用子查询，只要它返回的是单个值即可。很多包含子查询的语句都可以改写成连接查询。示例如下：

-- 更新语句（子查询写法）
update t_students set remark='考过满分' 
where id in(select t.studentid from t_examresults t where t.scores = 100);

-- 更新语句（连接写法）
update t_students set remark='考过满分' 
from t_students t1 join t_examresults t2 on t1.id = t2.studentid and t2.scores = 100;

-- 删除语句（子查询写法）
delete t_examresults where counts = 10 and studentid = (
    select t.id from t_students t where t.code = 's330104010');

-- 删除语句（连接写法）
delete t_examresults from t_examresults t1 
join t_students t2 on t1.studentid = t2.id and t1.counts = 10 and t2.code = 's330104010';

在 t-sql 中，包含子查询的语句和语义上等效的不包含子查询的语句在性能上通常是没有差别的。但在一些需要为外部查询的每个结果都执行内部查询的情况下，使用连接写法会产生更好的性能（如果数据很少，这种差别也很难体现出来），如某些非必须的相关子查询。示例如下：

-- 查询所有学生第 1 次课程 2 考试的成绩（子查询写法）
select (select t2.name from t_students t2 where t2.id = t1.studentid) studentname,scores 
from t_examresults t1 
where t1.counts = 1 and t1.courseid = 2;

-- 查询所有学生第 1 次课程 2 考试的成绩（连接写法）
select t2.name studentname,scores 
from t_examresults t1 
join t_students t2 on t1.studentid=t2.id 
where t1.counts = 1 and t1.courseid = 2;

2、公用表表达式 cte

在 t-sql 中，with 语句用于指定临时命名的结果集，这些结果集被称为公用表表达式(common table expression，简称 cte)。基本语法如下：

with cte-name (column-names) as (cte-query) [,...]

参数释义如下：

• cte-name 代表公用表表达式的有效标识符。类似于子查询的别名，在一个语句中不能出现重复的 cte-name，但可以与 cte 引用的基表名称相同。引用 cte 中的任何字段都得用 cte-name 来限定，而不能使用字段原本所属的基表来限定。
• column-names 代表公用表表达式的字段名列表，只要 column-name 的个数与 cte-query 中定义字段数相同即可。如果为 cte-query 中的所有字段都提供了不同的名称，那么 column-names 就是可选的了（一般大家都这么干，毕竟有谁会喜欢没必要的繁琐呢？）。
• cte-query 代表一个公用表表达式的查询语句，可以是任意合法的 select 语句。

2.1、普通公用表表达式

cte 可在单条 insert、delete、update 或 select 语句的执行范围内定义。

cte & insert 如要把 2000 年之前出生的女生信息插入到好学生表中，用 cte 定义女生数据，示例如下：

with temp as(
    select t.id,t.name,t.gender,t.birthday from t_students t where t.gender = 0
)
insert into t_goodstudents(id,name,gender,birthday) 
select * from temp where birthday < '2000-01-01';

cte & delete 如要把姓名和性别都是空的学生信息删除，用 cte 定义姓名为空的数据，示例如下：

with t as(
    select t.* from t_goodstudents t where t.name is null
)
delete from t where t.gender is null;

cte & update 如要把历次语文成绩的平均分更新到学生备注中，用 cte 定义学生平均分数据，示例如下：

with temp as(
    select t.studentid,t.courseid,avg(t.scores) avgscore 
    from t_examresults t 
    group by t.studentid,t.courseid
)
update t_students set remark = t1.avgscore 
from temp t1 
join t_courses t2 on t1.courseid = t2.id 
where t_students.id = t1.studentid and t2.name = '语文';

cte & select（多次引用同一个 cte）如要查询前 3 次考试的总成绩及平均成绩，用 cte 定义各次的成绩数据，示例如下：

with temp as(
    select t.studentid,t.counts,sum(t.scores) sumscore 
    from t_examresults t 
    where t.counts in(1,2,3) 
    group by t.studentid,t.counts
)
select t1.code,t1.name,
    t2.sumscore firstsumscore,t3.sumscore secondsumscore,t4.sumscore thirdsumscore,
    (t2.sumscore + t3.sumscore + t4.sumscore)/3 avgsumscore 
from t_students t1 
join temp t2 on t1.id = t2.studentid and t2.counts = 1 
join temp t3 on t1.id = t3.studentid and t3.counts = 2 
join temp t4 on t1.id = t4.studentid and t4.counts = 3;

cte & select（一个 with 定义多个 cte）如要查询男生们前 3 次课程 1 的考试成绩，用 cte 定义各次的成绩数据，示例如下：

with t1 as(
    select t.studentid,t.scores from t_examresults t where t.courseid = 1 and t.counts = 1
),
t2 as(
    select t.studentid,t.scores from t_examresults t where t.courseid = 1 and t.counts = 2
),
t3 as(
    select t.studentid,t.scores from t_examresults t where t.courseid = 1 and t.counts = 3
)
select t4.code,t4.name,t1.scores firstscore,t2.scores secondscore,t3.scores thirdscore 
from t_students t4 
join t1 on t4.id = t1.studentid 
join t2 on t4.id = t2.studentid 
join t3 on t4.id = t3.studentid 
where t4.gender = 1;

2.2、递归公用表表达式

cte 可以包含对自身的引用，这种表达式被称为递归公用表表达式。一个递归 cte 中至少要包含两个查询定义，一个定位点成员和一个递归成员，递归成员的 from 子句只能引用一次 cte。另外，定位点成员和递归成员二者的字段数必须相同，字段的数据类型也需要保持一致。

从上到下递归，如要查询浙江省及以下各级别的行政区，示例如下：

with temp as(
    select t1.id,t1.name from t_districts t1 where t1.code = '330000' 
    union all 
    select t2.id,t2.name from t_districts t2,temp t1 where t2.parentid = t1.id 
)
select temp.name from temp;

从下到上递归，如要查询西湖区及其所有上级行政区，示例如下：

with temp as(
    select t1.parentid,t1.name from t_districts t1 where t1.code = '330106' 
    union all 
    select t2.parentid,t2.name from t_districts t2,temp t1 where t2.id = t1.parentid 
)
select temp.name from temp;

可以定义多个定位点成员和递归成员，但必须将所有定位点成员查询定义置于第一个递归成员定义之前。在起点成员之间可以用任意集合运算符，而在最后一个定位点成员和第一个递归成员之间，以及多个递归成员之间，必须用 union all 来连接。示例如下（查询卢小妹的所有祖先）：

with temp(id) as(
    select t1.father from t_persons t1 where t1.name = '卢小妹' 
    union 
    select t2.mother from t_persons t2 where t2.name = '卢小妹' 
    union all 
    select t3.father from t_persons t3,temp where t3.id = temp.id 
    union all 
    select t4.mother from t_persons t4,temp where t4.id = temp.id 
)
select t1.id,t1.name,t1.father,t1.mother 
from t_persons t1,temp 
where t1.id=temp.id;

递归运算一定要有出口，否则就是死循环了！sql server 提供了一个 maxrecursion 提示来限制递归级数，以防止出现无限循环。但我个人觉得应该尽可能的通过 where 条件或业务逻辑来定义更合理的出口。例如要显示的限定只返回某一递归级别的数据，示例如下（查询浙江省下所有县一级的行政区）：

with temp as(
    select t1.id,t1.name,t1.code,t1.level 
    from t_districts t1 
    where t1.code = '330000' 
    union all 
    select t2.id,t2.name,t2.code,t2.level 
    from t_districts t2,temp 
    where t2.parentid = temp.id
)
select temp.code,temp.name,temp.level from temp where temp.level = 3;

尽管看上去很简单，但在实际开发中很可能并没有类似 level 这种标识级别的字段可用。如果是这样，那我们还可以通过递归成员的递归次数来实现同样的过滤效果。示例如下：

with temp as(
    select t1.id,t1.name,t1.code,t1.level,0 step 
    from t_districts t1 
    where t1.code = '330000' 
    union all 
    select t2.id,t2.name,t2.code,t2.level,temp.step + 1 
    from t_districts t2,temp 
    where t2.parentid = temp.id
)
select temp.code,temp.name,temp.level from temp where temp.step = 2;

3、本文小结

本文主要介绍了 t-sql 中最常见的几种子查询以及公用表表达式 cte。本文还专门说明了递归 cte，它可以实现类似于 pl/sql 中的 connect by 层次查询。

本文参考链接：

• 1、sql server 2016 subqueries
• 2、sql server 2016 with
• 3、.net程序员学用oracle系列之子查询

本文链接：
版权声明：本文为博客园博主 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！个人博客，能力有限，若有不当之处，敬请批评指正，谢谢！