将所有获取奖金的员工当前的薪水增加10%

drop table if exists emp_bonus; drop table if exists `salaries` ; create table emp_bonus( emp_no int not null, btype smallint not null); CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` float(11,1) default NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`)); insert into emp_bonus values(10001,1); INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22'); INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3372KB, 提交时间: 2021-12-04

update salaries
set salary = salary*1.1
where to_date = '9999-01-01'
and emp_no in (select e.emp_no from emp_bonus e join salaries on e.emp_no = salaries.emp_no)

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3372KB, 提交时间: 2021-09-17

update salaries
set salary= salary * 1.1 where to_date = '9999-01-01';

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3380KB, 提交时间: 2021-12-16

update salaries set salary = salary * 1.1 where to_date = '9999-01-01' and emp_no in (select emp_no from emp_bonus)

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3384KB, 提交时间: 2021-09-04

update salaries set salary=salary*1.1
where emp_no in (select emp_no from emp_bonus)
and to_date='9999-01-01'

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3476KB, 提交时间: 2021-09-12

UPDATE salaries 
SET salary = salary * 1.1 
WHERE
	to_date = '9999-01-01' 
	AND emp_no IN (
SELECT
	* 
FROM
	( SELECT DISTINCT ( salaries.emp_no ) FROM `salaries` INNER JOIN emp_bonus ON emp_bonus.emp_no = salaries.emp_no ) tmp1 
	)

emp_no	salary	from_date	to_date
10001 10001	85097.0 88958.0	2001-06-22 2002-06-22	2002-06-22 9999-01-01

emp_no	salary	from_date	to_date
10001 10001	85097.0 97853.8	2001-06-22 2002-06-22	2002-06-22 9999-01-01

详情