13、SQL Server 使用子查询和CTE提供数据之简单子查询

使用子查询和CTE提供数据之简单子查询

使用了简单子查询的查询执行顺序：

1、执行一次简单子查询

2、将结果传递给外部查询

3、执行一次外部查询

三种数据返回情况：

1、返回单个标量值

2、返回一个值列表(单列多行数据)

3、返回多列数据集

注：子查询类似于联接，它们都提供了在单个查询中引用多个数据源的途径，很多使用联接

的查询都可以重写为使用子查询的查询。

使用联接从两个数据源中取回数据后，可以对其进行筛选和操纵，如果必须在联接前对

数据进行操纵，应使用子查询。

一、公用表表达式（C T E）

可视为临时视图。

基本语法：With   CTEname 1(列名1 ，列名2，....)

          as  ( 子查询 )

          [，Ctename2 (列名1 ，列名2.....)

  as  (子查询) ]

可以同时定义多个CTE。

创建完以后，就可以将CTEname当作一般的表(或视图)使用。

CTE可以多次使用，且可执行复杂的查询，但有两个重要的缺点：

1、不同于子查询，CTE不能嵌套，即一个CTE不能包含另一个CTE

2、CTE不能引用主查询中的内容。和简单子查询一样，CTE必须是独立的。

注：对于在多个地方引用同一个子查询的极其复杂的查询来说，通过CTE可减少代码量，

并改善可读性，且CTE和子查询性能方面几乎无差。

如：

with selectbmid (bmid,zwname)

as (select bmid,zwname from zhiwei)

select * from selectbmid

select * from selectbmid --这一句会提示selectbmid无效。

注：CTE只对接下来的一条语句有效。

二、使用标量子查询

子查询只返回一个值。

注：有时可能需要使用函数 Cast( ) 、Convert( )对使用了标量值的组合值进行数据类型

转换。

如：--如部门的工资占所有工资的百分比

--第一步所有工资

select sum(basic_gz+jiaban_gz+jiangjin) as TheTotal from gongzi

--第二步计算各个部门的工资，

select sum(A.basic_gz+a.jiaban_gz+A.jiangjin) as 部门工资,bmname 所属部门 from gongzi A inner join yuangong B on A.ygid=B.id 

inner join bumen C on B.bmID=C.id group by bmname

--第三步计算各个部门占的比例。

select  所属部门,cast (

convert(decimal(4,2),(

部门.部门工资/

(select sum(basic_gz+jiaban_gz+jiangjin) as TheTotal from gongzi )

)*100) 

as varchar(5)

)+'%' as 工资份额 from (select sum(A.basic_gz+a.jiaban_gz+A.jiangjin) as 部门工资,bmname 所属部门 from gongzi A inner join yuangong B on A.ygid=B.id 

inner join bumen C on B.bmID=C.id group by bmname) AS 部门 

结果：

所属部门 工资份额

管理部 6.85%

技术部 53.36%

客户部 18.32%

销售部 21.47%

三、将子查询用作列表（一个列中的多个值）

：子查询必须只返回一列

子查询的功能也可用联接查询完成。根据查询的复杂程度，使用子查询的执行速度可能

更快，因为子查询在逐渐减少要处理的数据集行数。

如：

select id,zwname,bmid from zhiwei where bmid in (select id from bumen)

结果：

id zwname bmid

1 管理员 1

2 经理2 3

3 经理 3

4 实习生3 6

5 普工 1

6 组长 6

7 经理 4

8 经理 7

四、将子查询用作表

使用的条件是给子查询指定了范围变量。（别名）

经典应用：对于聚合函数问题，可以将子查询用作派生表。

因为创建聚合查询时，select中指定的列，都必须以某种方式参与聚合函数的计算

1、直接显示列值，必须出现在Group by子句中，

2、出现在聚合函数中参数聚合函数的计算

这种约束使得难以在查询中返回额外的描述信息，然后在子查询中执行聚合函数，并将找到

的行作为派生生传递给外部查询，让外部查询能够根据需要返回任何列。（一般使用

联接来联接子查询和外部查询）

如：

--查询出部门的集体工资数，并查出部门的基本信息。

--第一步，查询出部门的工资数，和关键列(可以能过这列来和外部查询进行联接)

select c.id 部门编号,sum(A.basic_gz+A.jiaban_gz+A.jiangjin) 部门工资  from gongzi A inner join yuangong B on A.ygid=B.id 

inner join bumen C on B.bmid=C.id GROUP BY C.id

--第二步，进行联接

select 部门编号,D.bmname 部门名称,部门工资 from bumen D inner join (select c.id 部门编号,sum(A.basic_gz+A.jiaban_gz+A.jiangjin) 部门工资  from gongzi A inner join yuangong B on A.ygid=B.id 

inner join bumen C on B.bmid=C.id GROUP BY C.id) E

on D.id=E.部门编号

--这里的bmname就没有出现在group by 也没有出现在聚合函数中