创建 emp_bonus-奖金表 并插入数据
CREATE TABLE emp_bonus
(
empno VARCHAR(10),
received DATE,
TYPE CHAR(5)
)
INSERT INTO emp_bonus(empno,received,TYPE) VALUES
('7934', '2017-05-17','1'),
('7934', '2017-02-15','2'),
('7839', '2017-02-15','3'),
('7782', '2017-02-15','1')
创建 emp-员工表 并插入数据
CREATE TABLE emp
(
empno VARCHAR(10),
ename VARCHAR(10),
deptno CHAR(5),
sal CHAR(8)
)
INSERT INTO emp(empno,ename,deptno,sal) VALUES
('7934', 'miller','10','1300'),
('7839', 'king','10','5000'),
('7782', 'clark','10','2450')
根据用户的类型来发奖金，1 * 0.1; 2 * 0.2 ,3 * 0.3
SELECT a.empno,a.ename,a.deptno,a.sal,(CASE WHEN b.type= 1 THEN a.sal*0.1
WHEN b.type= 2 THEN a.sal*0.2
WHEN b.type= 3 THEN a.sal*0.3 END) bonus
FROM emp a
JOIN emp_bonus b
ON a.empno=b.empno
要求计算:共发了多少工资和奖金
PS : 难点在于不同粒度的sum
方法一：
SELECT SUM(c.sal) ,SUM(c.bonus)
FROM ( SELECT z.empno,MAX(z.sal) sal,SUM(z.bonus) bonus
FROM ( SELECT a.empno,a.ename,a.deptno,a.sal
,(CASE WHEN b.type= 1 THEN a.sal*0.1
WHEN b.type= 2 THEN a.sal*0.2
WHEN b.type= 3 THEN a.sal*0.3 END) bonus
FROM emp a
JOIN emp_bonus b
ON a.empno=b.empno) z
GROUP BY z.empno
)c
方法二：
SELECT SUM(a.sal),SUM(a.sal*b.rate)
FROM emp a
JOIN ( SELECT empno,SUM(CASE WHEN TYPE= 1 THEN 0.1
WHEN TYPE= 2 THEN 0.2
WHEN TYPE= 3 THEN 0.3 END) AS rate
FROM emp_bonus
GROUP BY empno)b
ON a.empno=b.empno
GROUP BY a.deptno
