第六题:查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序
思路:SELECT,输出:emp_no,salary,排序:ORDER BY emp_no
首先:SELECT employees.emp_no, salary FROM salaries,employees WHERE employees.emp_no=salaries.emp_no ORDER BY salaries.emp_no DESC
测试没通过,原因:入职时候,根据测试用例可以看出salary表中每一个emp_no都有多个薪水对应不同的时间,所以应该对每一个emp_no都找出时间最早的薪水然后输出
SELECT employees.emp_no, salary FROM salaries,employees WHERE from_date=( SELECT min(from_date) FROM salaries WHERE salaries.emp_no=employees.emp_no) ORDER BY salaries.emp_no DESC
在电脑上调试对着呢,在牛客上通过不了,什么原因呢?
select emp_no,salary from salaries
group by emp_no having min(from_date)
order by emp_no DESC
但是看到牛客上也有用min()来做的,不过HAVING开销大
答案:
SELECT employees.emp_no, salary FROM salaries,employees
WHERE employees.emp_no=salaries.emp_no AND from_date=hire_date ORDER BY employees.emp_no DESC;
审题要仔细,在employees表中已经给了员工的入职时间,只要根据入职时间找就可以了。
第七题:查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
思路:分组,分组的过滤条件要用HAVING,别名:alias,AS
SELECT emp_no, COUNT(from_date) AS t FROM salaries GROUP BY emp_no HAVING COUNT(from_date)>15
第八题:找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
思路:只显示一次:DISTINCT,逆序:ORDER BY .. DESC
SELECT DISTINCT salary FROM salaries WHERE to_date='9999-01-01' ORDER BY salary DESC
第九题:获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary
思路:一次性写对了。。。就是基本的操作了
SELECT d.dept_no, d.emp_no, s.salary FROM dept_manager d, salaries s
WHERE d.to_date='9999-01-01' AND s.to_date='9999-01-01' AND d.emp_no=s.emp_no
第十题:获取所有非manager的员工emp_no
思路:非:not in
SELECT emp_no FROM employees WHERE employees.emp_no NOT IN (SELECT emp_no FROM dept_manager)
但是感觉应该有其他的方法,左连接
SELECT employees.emp_no FROM employees LEFT JOIN dept_manager ON employees.emp_no==dept_manager.emp_no WHERE dept_manager.emp_no IS NULL