一头扎进sql之多表操作
一、多表查询时NULL值处理
- 要求返回比"allen"工资低的所有员工
select a.ename,a.conn from emp a where a.conn < (select b.conn from emp b where b.ename = 'allen') ;
- 正确的写法应该是
select a.ename,a.conn from emp a where coalesce(a.conn,0) < (select b.conn from emp b from b.ename = 'allen');
- 注意点
NULL值比较结果还是NULL，所以这里需要进行NULL值的一个转化才行
二、聚集和外链接
- 要求返回所有部门的工资和奖金
select e.deptno,
sum(e.sal) as total_sal,
sum(e.sal*eb2.rount) as total_bonus,
from emp
left join (select eb.empno,
sum(case when type =1 then 0.1
when type =2 then 0.2
when type =3 then 0.3 end ) as tote
from emp_bonux eb
group by eb.empno) eb2 on eb2.empno = e.deptno
group by e.deptno
oder by 1;
- 多个表返回丢失的数据用FULL JOIN
三、聚集和内连接
- 要求返回部门为10的所有的员工的工资和奖金
select e.deptno
e.empno,
e.ename,
(e.sal * CASE WHEN type =1 THEN 0.1
WHEN type =2 THEN 0.2
WHEN type =3 THEN 0.3 end ) as bonus
from emp e
inner join emp_bonus eb on eb.empno = eb.empno
where d.deptno = 10
order by 1,2
如果聚合的话必须先把奖金按照员工汇总然后在进行聚集
select e.deptno
sum(e.sal) as total_sal,
sum(e.sal*eb.bonus) as total_bonus
from emp e
inner join (select eb.empno,
case when type =1 then 0.1
when type =1 then 0.2
when type =1 then 0.3 end) as bonus
group by eb.empno) eb2 on eb2.empno = e.empno
where e.deptno = 10;
group by e.deptno;
四、UNION ALL 、UNION 、和空字符串
select ename as 部门名称 ,denpno 部门编号, evl(mgr,deptno) as 上级编码
NNION ALL
select ename as 部门名称 ,denpno 部门编号, NULL as 上级编码 （应该用‘’空字符串）
- 当有重复数据时必须加上唯一标识
74990 30 74990 30
7521 30 7521 30
7654 30 7654 30
7844 30 7844 30
7900 30
这两条数据坐聚合操作就会出现 OR就是有5条数据 而用 UNION就会只出现一条数据 解决办法就是加入唯一字段
select empno,deptno from emp where mgr = 7684
UNION
select empno,deptno from emp where job = 'SALEMAN' 就可以保证正确的去重数据
select disdintc deptno from (
select empno,deptno from emp where mgr = 7684
UNION ALL
select empno,deptno from emp where job = 'SALEMAN'
)
order by deptno;
五、组合相关的行
- 显示部门为10的员工编码、姓名、部门名称和工作地址
select e.deptno,e.ename,e.dname,e.loc
from emp e
inner join dept d on (e.deptno = d.deptno)
where e.deptno = 10
select e.empno,e.ename,d.dname,d.loc
from emp e,dept d
e.deptno = d.deptno and e.deptno = 10
六、组合相关的行
（LEFT JOIN） （ RIGHT JOIN） （INNER JOIN ） （ FULL JOIN ）
自关联可以采用 left join进行查询
