- Q1：选择最晚入职的员工
知识点：子查询
SELECT *
FROM employees
WHERE hire_date = (
SELECT max(hire_date)
FROM employees
);
- Q2：倒数第三入职的员工
知识点：limit 1 offset 2
请注意distinct的用法！
SELECT *
FROM employees
WHERE hire_date = (
SELECT distinct hire_date
FROM employees
ORDER BY hire_date DESC limit 1 offset 2
);
-
Q7：薪水记录超过15次
知识点：group by和having的用法
引申：having和where的区别，SQL执行的顺序是where、select、having，因此where是在对记录进行的过滤，而having是对结果集进行的过滤。下面这里超过15次明显应该在结果集之后才进行过滤，因此使用having！
select emp_no, count(*) as t from salaries GROUP by emp_no having t > 15; -
Q8：薪水情况且不重复
1、select distinct salary from salaries order by salary desc;
2、select salary from salaries group by salary order by salary desc;
用group by也可以实现去重，并且效率更高，但为啥group by比distinct效率更高呢？请见group by和distinct区别 -
Q10： 获取所有非manager的员工emp_no
知识点：等号只有一个=，NULL判断要用is
SELECT em.emp_no
FROM employees em
LEFT JOIN dept_manager dp ON em.emp_no = dp.emp_no
WHERE dp.emp_no IS NULL; -
Q12：获取每个部门中当前员工薪水最高的相关信息
同部门多个最多的工资会返回多条：group找到最大的工资后再join找出对应的emp_no
SELECT t.dept_no
,emp_no
,maxSalary
FROM (
SELECT dept_no
,dept_emp.emp_no
,salary
FROM dept_emp
INNER JOIN salaries ON dept_emp.emp_no = salaries.emp_no
) t
INNER JOIN (
SELECT dept_no
,max(salary) AS maxSalary
FROM dept_emp
INNER JOIN salaries ON dept_emp.emp_no = salaries.emp_no
GROUP BY dept_no
) AS r
ON t.dept_no = r.dept_no AND t.salary = r.maxSalary
ORDER BY dept_no;
第二种解法只取一个，无法理解？
select r.dept_no,r.emp_no,max(r.salary) from (
select d.dept_no,d.emp_no,s.salary from dept_emp d,salaries s
where d.emp_no=s.emp_no·
and d.to_date='9999-01-01'
and s.to_date='9999-01-01'
order by s.salary desc
)as r
group by r.dept_no
order by r.dept_no asc
另外一种开窗函数的解法
SELECT dept_no
,emp_no
,salary AS maxSalary
FROM (
SELECT dept_no
,emp_no
,salary
,row_number() OVER (
PARTITION BY dept_no ORDER BY salary desc
) AS rn
FROM (
SELECT dept_emp.emp_no
,dept_emp.dept_no
,salaries.salary
FROM dept_emp
INNER JOIN salaries ON dept_emp.emp_no = salaries.emp_no
) AS t_dept_salaries
) AS t_dept_salaries_ordered
WHERE rn = 1;
- Q17：获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
主要的问题，同一个薪水可能有多个员工，因此排序筛选的时候要注意下。
方法1：使用开窗函数获取排名，然后筛选
DENSE_RANK可以在薪水一样的前提下获取到相同的排名
SELECT emp_no
,salary
FROM (
SELECT emp_no
,salary
,DENSE_RANK() OVER (
ORDER BY salary DESC
) AS r
FROM salaries
) AS t_rank
WHERE r = 2;
方法2：使用子查询获取第二多的薪水，然后筛选
SELECT emp_no
,salary
FROM salaries
WHERE salary = (
SELECT DISTINCT salary
FROM salaries
ORDER BY salary DESC limit 1 offset 1
);
- Q18：获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
题目的限制条件是不能使用rank
思路1：先排除掉最高，再取最大
SELECT e.emp_no
,s.salary
,e.last_name
,e.first_name
FROM salaries s
INNER JOIN employees e ON s.emp_no = e.emp_no
WHERE salary = (
SELECT max(salary)
FROM salaries
WHERE salary != (
SELECT max(salary)
FROM salaries
)
);
方法2：使用inner join找到两个比它大于等于的薪水，好处是可以得到任意大的薪水
SELECT e.emp_no
,s.salary
,e.last_name
,e.first_name
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no
AND s.salary = (
SELECT s1.salary
FROM salaries s1
INNER JOIN salaries s2 ON s1.salary < s2.salary
GROUP BY s1.salary
HAVING count(*) = 1
)
- Q21：查找在职员工自入职以来的薪水涨幅情况
知识点：两个表相减可以先join再用列的运算
方法1：先找出入职薪水，再找出现在的薪水，然后join，再相减
SELECT current_s.emp_no
,current_s.salary - entry_s.salary as growth
FROM (
SELECT emp_no
,salary
FROM salaries
WHERE to_date = "9999-01-01"
) AS current_s
INNER JOIN (
SELECT e.emp_no
,salary
FROM employees e
INNER JOIN salaries s ON e.hire_date = s.from_date
) AS entry_s ON current_s.emp_no = entry_s.emp_no
ORDER by growth;
- Q23：对所有员工的薪水按照salary降序进行1-N的排名
方法1：利用dense_rank加开窗函数
SELECT emp_no
,salary
,DENSE_RANK() OVER (
ORDER BY salary DESC
)
FROM salaries
ORDER BY salary DESC;
方法2：利用本身之间的join，注意DISTINCT的使用
SELECT s1.emp_no
,s1.salary
,count(DISTINCT s2.salary) AS rank
FROM salaries s1
INNER JOIN salaries s2 ON s1.emp_no = s2.emp_no
AND s1.salary <= s2.salary
GROUP BY s1.emp_no
ORDER BY s1.salary DESC;
- Q30：使用子查询的方式找出属于Action分类的所有电影对应的title,descr
要求不适用join，使用子查询，体会一下
SELECT f.title
,f.description
FROM film f
WHERE f.film_id IN (
SELECT film_id
FROM film_category fc
WHERE fc.category_id = (
SELECT category_id
FROM category
WHERE name = 'Action'
)
)
网友评论