条件:
1 CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, 2 `salary` int(11) NOT NULL, 3 `from_date` date NOT NULL, 4 `to_date` date NOT NULL, 5 PRIMARY KEY (`emp_no`,`from_date`));
要求:
1、输出emp_no,salary,running_total;
2、running_total为前N个当前( to_date = ‘9999-01-01‘)员工的salary累计和。
解析:
本题的难点是计算running_total,emp_no在哪一行,running_total就累加到哪一个emp_no
应以emp_no为线索,运用子查询对salary进行累加
to_date的条件也是令人忽视的点,外部查询和子查询都需要
代码:
1 select emp_no, salary, 2 3 ( select sum(salary) from salaries as s2 where s1.emp_no >= s2.emp_no and s2.to_date = ‘9999-01-01‘ ) as running_total 4 5 from salaries as s1 6 7 where s1.to_date = ‘9999-01-01‘ ;