[程式設計題]查詢員工編號emp_no為10001其自入職以來的薪水salary漲幅值growth

連結：https://www.nowcoder.com/questionTerminal/c727647886004942a89848e2b5130dc2
來源：牛客網
 

[程式設計題]查詢員工編號emp_no為10001其自入職以來的薪水salary漲幅值growth

• 熱度指數：23422 時間限制：1秒 空間限制：32768K
• 演算法知識視訊講解

查詢員工編號emp_no為10001其自入職以來的薪水salary漲幅值growth
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

輸入描述:

無

 

輸出描述:

growth
28841

示例1

輸入

無

輸出

無

--查詢員工編號emp_no為10001其自入職以來的薪水salary漲幅值growth
SELECT
	( MAX( salary ) - MIN( salary ) ) AS growth 
FROM
	salaries 
WHERE
	emp_no = 10001;
--第二種方法
SELECT (
		SELECT salary
		FROM salaries
		WHERE emp_no = 10001
		ORDER BY to_date DESC
		LIMIT 1
	) - (
		SELECT salary
		FROM salaries
		WHERE emp_no = 10001
		ORDER BY to_date ASC
		LIMIT 1
	) AS growth;
--第三種方法
SELECT s4.salary - s1.salary AS growth
FROM salaries s1
	INNER JOIN (
		SELECT MIN(s2.from_date) AS firstDate, MAX(s2.from_date) AS lastDate, s2.emp_no
		FROM salaries s2
		WHERE s2.emp_no = '10001'
	) s3
	ON s1.emp_no = s3.emp_no
		AND s1.from_date = s3.firstDate
	INNER JOIN salaries s4
	ON s3.emp_no = s4.emp_no
		AND s3.lastDate = s4.from_date;