亲宝软件园·资讯

展开

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

族语1996 人气:1
### 新增连接查询而不影响其他连接查询 请看图: ![](https://img2020.cnblogs.com/blog/1289794/202003/1289794-20200322224315615-1346978573.png) 这种情况我们一般会使用左连接的方式。 ``` 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 ``` ![](https://img2020.cnblogs.com/blog/1289794/202003/1289794-20200322231323506-991329788.png) 现在只需要看到上图的表。 现在有一个需要,就是要统计上面部门为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 ``` ![](https://img2020.cnblogs.com/blog/1289794/202003/1289794-20200322231727812-407566200.png) 得到的结果为错误的。因为有人得到两次奖励: ![](https://img2020.cnblogs.com/blog/1289794/202003/1289794-20200322231901975-1605681221.jpg) 那么可能会这样写: ``` sql (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没显示出来 ``` sql 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。 优化一下: ``` sql 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 函数去写: ``` sql 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相同。 所以这种情况尽量不要去使用这种方式。

加载全部内容

相关教程
猜你喜欢
用户评论