改写优化SQL（3）：聚合标量子查询改为left join

聚合标量子查询，是标量子查询的升级版，因为不是简单的返回一个字段，而是要对字段求 sum，avg等，也就是标量子查询+聚合函数。

但是这种写法，本质上还是标量子查询的写法，所以效率不高，建议改为left join方式。

（1）建表

CREATE TABLE tb_emp  
(  
emp_id INT NOT NULL PRIMARY KEY CLUSTERED,  
emp_name VARCHAR(20) NOT NULL,  
sal numeric(10,2),   
dept_id int
)  
  
INSERT INTO dbo.tb_emp  
VALUES  
(1, '张三',5000,1),  
(2, '李四',5500,1),  
(3, '王五',8000,3),  
(4, '孙六',6000,4),  
(5, '半泽直树',10000,4),
(6, '小刘',7500,4),  
(7, '小明',5000,5),
(8, '小张',4500,2),  
(9, '小王',8000,5)

  
CREATE TABLE tb_dept  
(  
dept_id INT NOT NULL PRIMARY KEY CLUSTERED,  
dept_name VARCHAR(30) NOT NULL,  
)  
  
  
INSERT INTO tb_dept  
VALUES  
(1, '财务部'),  
(2, '行政部'),  
(3, '公关部'),  
(4, '营业二部'),  
(5, '营业一部');  

（2）聚合标量子查询写法

现在要求每个部门的总工资、平均工资，代码如小：

select dept_id,
       dept_name,
	   (select sum(sal) from tb_emp e where e.dept_id = d.dept_id) sal_sum,
	   (select avg(sal) from tb_emp e where e.dept_id = d.dept_id) sal_avg
from tb_dept d
/*
dept_id	dept_name	sal_sum	sal_avg
1	财务部	10500.00	5250.000000
2	行政部	4500.00	4500.000000
3	公关部	8000.00	8000.000000
4	营业二部	23500.00	7833.333333
5	营业一部	13000.00	6500.000000
*/

执行计划：

（3）改为left join写法

这种改写的思路就是 先分组聚合求值，然后再left join。

select d.dept_id,
       d.dept_name,
	   e.sal_sum,
	   e.sal_avg
from tb_dept d
left join
(
	select dept_id,
	       sum(sal) sal_sum,
		   avg(sal) sal_avg
	from tb_emp
	group by dept_id
)e
 on e.dept_id = d.dept_id
/*
dept_id	dept_name	sal_sum	sal_avg
1	财务部	10500.00	5250.000000
2	行政部	4500.00	4500.000000
3	公关部	8000.00	8000.000000
4	营业二部	23500.00	7833.333333
5	营业一部	13000.00	6500.000000
*/

执行计划：

从结果看，2个sql的结果是一样的，说明改写是正确的，另外，从执行计划分析，聚合标量子查询的写法访问了2次tb_emp表，而left join的方式只用了一次tb_emp。

所以，left join的效率更高。