组合连接和聚合函数

select deptno,
          sum(distinct sal) as total_sal,
          sum(bonus) as total_bonus
          from (
select e.empno,
          e.ename,
          e.sal,
          e.deptno,
          e.sal*case when eb.type = 1 then .1
                           when eb.type  =2 then .2
                           else .3 end as bonus
  from emp e,emp_bonus eb
  where e.empno = eb.empno
     and e.deptno = 10
            )x
 group by deptno

如果没有distinct，连接之后，一个员工可能有俩种类型的薪水，但是只能有一次工资。会造成工资重复计算。

select deptno,
          sum(distinct sal) as total_sal,
          sum(bonus) as total_bonus
          from (
select e.empno,
          e.ename,
          e.sal,
          e.deptno,
          e.sal*case when eb.type = 1 then .1
                           when eb.type  =2 then .2
                           else .3 end as bonus
  from *emp e left join emp_bonus eb
  on e.empno = eb.empno*
     where e.deptno = 10
            )x
 group by deptno

若奖金表的员工不齐，做内连接丢失数据。改为左外连接。