20220721 SQL server 子查询

20220721 SQL server 子查询

子查询，又叫做嵌套查询。

将一个查询语句做为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当作结果集的查询语句被称为子查询。

子查询有两种类型：

一种是只返回一个单值的子查询，这时它可以用在一个单值可以使用的地方，这时子查询可以看作是一个拥有返回值的函数；

另外一种是返回一列值的子查询，这时子查询可以看作是一个在内存中临时存在的数据表。

参考：SQL——子查询

一、子查询案例

1. 原始数据表

如：我们现在有一张学生成绩表：

select * from stu_score 
order by stu_no,subject

里面有学生的学号、姓名、班级、科目、成绩等信息。

2. 要求

要求：显示出所有科目的平均成绩，以及学生该科成绩与平均成绩的大小关系。
如图所示：

要想实现上面的要求，我们应该先求出各科的平均成绩；然后将原始表和平均成绩表进行关联；最后，用cast新建条件列，根据每个学生各科目成绩和该科目平均成绩的大小关系，生成不同的值。

3. 实现步骤

① 求各科平均成绩

select subject,avg(score) score_avg 
from stu_score
group by subject

② 关联查询原始表和平均成绩表

现在，我们得到了学生各科目的平均成绩表(假设为b表)。
我们需要用b表去和原始的学生成绩表(假设为a表)进行关联，关联字段为“subject(科目)”。
这时候我们就需要用到子查询，将b表作为子查询，去和a表关联。
(其实子查询就是将查询结果作为新的表，再去进行查询操作。)

select * from stu_score a
join 
(
select subject,avg(score) score_avg
from stu_score
group by subject
) b
on a.subject=b.subject
order by a.stu_no,a.subject

③ 新建条件列

cast根据学生成绩与平均成绩的大小关系，生成不同的值。

select 
	a.stu_no,a.name,
	a.subject,a.score,
	b.score_avg,
(
case when a.score>b.score_avg 
	then 'above avg'  --大于平均成绩
when a.score<b.score_avg 
	then 'below avg'  --小于平均成绩
else 'equal avg'  --等于平均成绩
end
) 'score_distribution'
from stu_score a
join (
select subject,avg(score) score_avg
from stu_score
group by subject) b  --平均成绩表作为子表，进行关联查询
on a.subject=b.subject
order by a.stu_no,a.subject

近期已学内容回顾：

20220718 Leetcode练习内容
20220719 开窗函数1：ROW_NUMBER()
20220720 开窗函数2：rank()/dense_rank()
SQL Server 中SQL语句执行顺序
SQL中常见的英语词汇