1、查找員工編號emp_now為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`));

解題思路：巧妙使用max和min。

select (max(salary)-min(salary)) as groeth from salaries
where emp_no='10001'

2、

查找所有員工自入職以來的薪水漲幅情況，給出員工編號emp_no以及其對應的薪水漲幅growth，并按照growth進行升序
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
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`));

解題思路：

先用內連接構造兩張當前薪水和入職薪水表，然后再內連接這兩張表，篩選出emp_no和growth

select currentSalary.emp_no,(currentSalary.salary-beginSalary.salary) as growth from (select e.emp_no,s.salary ??????from employees as e inner join salaries as s ??????on e.emp_no=s.emp_no where s.to_date='9999-01-01') as currentSalary inner join(select e.emp_no,s.salary ??????from employees as e inner join salaries as s ??????on e.emp_no=s.emp_no where s.from_date=e.hire_date) as beginSalary on currentSalary.emp_no=beginSalary.emp_no order by growth

轉載于:https://www.cnblogs.com/chenxianghui/p/8086876.html

總結

以上是生活随笔為你收集整理的经典SQL题库的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。