使用子查询和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 也没有出现在聚合函数中