sql 语句系列(多表之链)[八百章之第三章]

新增连接查询而不影响其他连接查询

请看图:

这种情况我们一般会使用左连接的方式。

select e.ENAME,d.LOC,eb.RECEIVED
from emp e join dept d
on(e.DEPTNO=d.DEPTNO) left join emp_bonus eb
on(eb.EMPNO=e.EMPNO)
order by 2

上面这种可以实现的，但是不利于我们在写code语句中的复用。

下面是一种标量子查询我的方式，可以帮助我们复用部分sql。

select e.ENAME,d.LOC,(select eb.RECEIVED from emp_bonus eb where e.EMPNO=eb.EMPNO) as RECEIVED
from emp e join dept d
on(e.DEPTNO=d.DEPTNO)
order by 2

这里原理很简单，其实就是先设置了RECEIVED 行然后去查询。
同样这里有限制就是RECEIVED 查询出来必须只有一个结果，因为开辟了一个空间。

在未来第600章中会介绍如果针对查询出多行的问题。

组合使用连接查询与聚合函数

select e.DEPTNO,e.EMPNO,e.ENAME,e.SAL,e.SAL*case 
when eb.TYPE=1 then 0.1 
when eb.TYPE=2 then 0.2
when eb.TYPE=3 then 0.3
end as bonus
from EMP e,emp_bonus eb
where e.EMPNO=eb.EMPNO
and e.DEPTNO=10

现在只需要看到上图的表。

现在有一个需要，就是要统计上面部门为10的SAL 和 bonus。

这个时候一般想到的是聚合函数。

select x.DEPTNO,sum(x.SAL) as total_sum,sum(x.bonus) as total_bonus from
(select  e.DEPTNO,e.EMPNO,e.ENAME,e.SAL,e.SAL*case 
when eb.TYPE=1 then 0.1 
when eb.TYPE=2 then 0.2
when eb.TYPE=3 then 0.3
end as bonus
from EMP e,emp_bonus eb
where e.EMPNO=eb.EMPNO
and e.DEPTNO=10) x
group by x.DEPTNO

得到的结果为错误的。因为有人得到两次奖励:

那么可能会这样写:

(select  e.DEPTNO,e.EMPNO,e.ENAME,e.SAL,e.SAL*case 
when eb.TYPE=1 then 0.1 
when eb.TYPE=2 then 0.2
when eb.TYPE=3 then 0.3
end as bonus
from EMP e,emp_bonus eb
where e.EMPNO=eb.EMPNO
and e.DEPTNO=10) x
group by x.DEPTNO

排除掉sal中相同的项增加，但是万一有人sal相同怎么办?这肯定是一个问题。还有一个问题就是如果这个部门有一部分人如果没有得到bonus怎么办？也就是说有一部分SAL没显示出来

select x.DEPTNO, d.total_sum,sum(x.bonus) as total_bonus from
(select  e.DEPTNO,e.EMPNO,e.ENAME,e.SAL,e.SAL*case 
when eb.TYPE=1 then 0.1 
when eb.TYPE=2 then 0.2
when eb.TYPE=3 then 0.3
end as bonus
from EMP e,emp_bonus eb
where e.EMPNO=eb.EMPNO
and e.DEPTNO=10
) x,(select DEPTNO,sum(EMP.SAL) as total_sum from EMP where EMP.DEPTNO=10 group by EMP.DEPTNO) d
where d.DEPTNO=x.DEPTNO
group by x.DEPTNO,d.total_sum

我们可以通过之查询出sum(x.bonus),然后再外表连接出d.total_sum。

优化一下：

select  e.DEPTNO,d.total_sum,sum(e.SAL*case 
when eb.TYPE=1 then 0.1 
when eb.TYPE=2 then 0.2
when eb.TYPE=3 then 0.3
end) as bonus
from EMP e,emp_bonus eb,(select DEPTNO,sum(EMP.SAL) as total_sum from EMP where DEPTNO=10 group by DEPTNO) d
where e.EMPNO=eb.EMPNO and e.DEPTNO=d.DEPTNO 
group by e.DEPTNO,d.total_sum

优化的依据是:
出现两个EMP.DEPTNO=10 条件可以合并,第二点就是没必要查e.ENAME这些，可以直接合并。
有些人可能使用sum over 函数去写:

select  e.DEPTNO,sum(distinct e.SAL) over (partition by e.deptno) as total_sum,sum(e.SAL*case 
when eb.TYPE=1 then 0.1 
when eb.TYPE=2 then 0.2
when eb.TYPE=3 then 0.3
end) over (partition by e.deptno) as bonus
from EMP e left join emp_bonus eb on e.EMPNO=eb.EMPNO
where e.DEPTNO=10

我上面使用了外连接，是避免这个部门有一部分人如果没有得到bonus。
其中有两个问题，一个就是over 语句中不能包括distinct了，第二个就是不同人empno 中可能sal相同。
所以这种情况尽量不要去使用这种方式。