平均工资

INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');

avg_salary
73292

示例1

输入:

drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` float(11,3) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');

输出:

73292.000

原站题解

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

select avg(salary)
from salaries
where to_date = '9999-01-01' and (
salary != (select max(salary) 
		   from salaries where to_date ='9999-01-01') and  
salary != (select min(salary) 
		   from salaries where to_date = '9999-01-01'))

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

select avg(salary)
from salaries 
where to_date='9999-01-01' and salary not in (select min(salary)
                                             from salaries
                                             where to_date='9999-01-01')
                                             and salary not in 
                                             (select max(salary)
                                             from salaries
                                             where to_date='9999-01-01');

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

select avg(salary) as avg_salary from salaries
where to_date='9999-01-01'
and salary not in (select max(salary) from salaries where to_date='9999-01-01')
and salary not in (select min(salary) from salaries where to_date='9999-01-01')

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

select avg(salary) from salaries 
where to_date = '9999-01-01' and salary not in(select max(salary) from salaries where to_date = '9999-01-01') and 
salary not in(select min(salary) from salaries where to_date = '9999-01-01')

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

SELECT AVG(s1.salary) AS avg_salary
FROM salaries AS s1
WHERE s1.to_date = '9999-01-01'
AND s1.salary NOT IN (SELECT MAX(salary) FROM salaries AS s2 WHERE s2.to_date = '9999-01-01')
AND s1.saLary NOT IN (SELECT MIN(salary) FROM salaries AS s2 WHERE s2.to_date = '9999-01-01');

详情