牛客MySQL练习题

2022-01-17 本文已影响0人抬头挺胸才算活着

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'
                )
        )

牛客MySQL练习题

猜你喜欢

热点阅读