sql题解及笔记209-219

209

SELECT emp_no, birth_date, first_name, last_name, gender, hire_date
FROM employees
WHERE emp_no % 2 = 1
AND last_name != 'Mary'
ORDER BY hire_date DESC;

210

SELECT t.title, AVG(s.salary) AS avg_salary
FROM titles t
JOIN salaries s ON t.emp_no = s.emp_no
WHERE t.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'--老是忘记
GROUP BY t.title
ORDER BY avg_salary ASC;

211 第二高

SELECT emp_no, salary
FROM salaries
WHERE salary = (
    SELECT DISTINCT salary
    FROM salaries
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1 --又忘记了
)
ORDER BY emp_no ASC;

题目类似，第X高/大

    SELECT DISTINCT xx FROM yy
    ORDER BY xx DESC
    LIMIT 1 OFFSET ? 

212 不能使用order by完成

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
WHERE s.salary = ( --第二高工资作为查询条件
    SELECT MAX(salary) --查出第二高工资
    FROM salaries 
    WHERE salary < (
        SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01' -- 原表最高工资
    )
    AND to_date = '9999-01-01'
)
AND to_date = '9999-01-01';

213  ？

SELECT e.last_name, e.first_name, d.dept_name
FROM employees e
LEFT JOIN dept_emp de ON e.emp_no = de.emp_no --没有分配到部门的员工
LEFT JOIN departments d ON de.dept_no = d.dept_no; --左连接从departments获取部门名称

三表连接，注意顺序

215 ？？

-- 定义第一个 CTE 获取每个员工的当前薪水
WITH current_salaries AS (
    SELECT emp_no, salary AS current_salary
    FROM salaries
    WHERE to_date = '9999-01-01' -- 过滤出当前在职员工的薪水记录
),--连续定义

-- 定义第二个 CTE 获取每个员工的入职时的初始薪水
initial_salaries AS (
    SELECT emp_no, MIN(salary) AS initial_salary -- 使用MIN函数找到每个员工入职以来最早的薪水
    FROM salaries
    GROUP BY emp_no -- 按照员工编号进行分组，确保每个员工只取最早的薪水
)

-- 主查询用来计算薪水的涨幅
SELECT  cs.emp_no,(cs.current_salary - isal.initial_salary) AS growth
FROM current_salaries cs
JOIN initial_salaries isal 
    ON cs.emp_no = isal.emp_no -- 通过员工编号将两个CTE的结果集进行连接
ORDER BY growth ASC;

获取当前在职员工的当前薪水和入职以来的初始薪水

时间过长，注意优化

CTE ：临时表，可以先计算一部分结果，再在主查询中复用这些结果

WITH 
cte1 AS (
    -- 第一个 CTE 的查询逻辑
    SELECT ...
),
cte2 AS (
    -- 第二个 CTE 的查询逻辑
    SELECT ...
)

216 ？？

SELECT d.dept_no, d.dept_name, COUNT(s.emp_no) AS sum --部门记录数就是员工工资记录数
FROM departments d --获取每个部门的编号和名称
--作为起点是因为要统计每个部门的工资记录数，表包含了部门的基本信息
--如果某个部门在 salaries 表中没有任何记录，但仍需要显示在结果中
JOIN dept_emp de ON d.dept_no = de.dept_no --获取属于每个部门的员工编号
JOIN salaries s ON de.emp_no = s.emp_no --统计这些员工的工资记录
GROUP BY d.dept_no, d.dept_name --按照部门编号和部门名称分组
ORDER BY d.dept_no ASC;

217 1-N的排名 ？窗口函数

SELECT emp_no, salary, 
       DENSE_RANK() OVER (ORDER BY salary DESC, emp_no ASC) AS t_rank
FROM salaries;

OVER表示排序规则

218

SELECT de.dept_no, e.emp_no, s.salary
FROM dept_emp de
JOIN employees e ON e.emp_no = de.emp_no
JOIN salaries s ON s.emp_no = e.emp_no
LEFT JOIN dept_manager dm ON dm.emp_no = e.emp_no
WHERE dm.emp_no IS NULL; 

题型类似，都是连接连接连接，选经理表空

219

SELECT de.emp_no, dm.emp_no AS manager_no, s1.salary AS emp_salary, s2.salary AS manager_salary
FROM dept_emp de
JOIN dept_manager dm ON de.dept_no = dm.dept_no --通过部门编号将员工和经理对应起来
JOIN salaries s1 ON de.emp_no = s1.emp_no --获取每个员工的薪水信息
JOIN salaries s2 ON dm.emp_no = s2.emp_no --获取每个经理的薪水信息
WHERE s1.to_date = '9999-01-01'
  AND s2.to_date = '9999-01-01'
  AND s1.salary > s2.salary;

目标：员工当前的薪水比他们的经理当前薪水高的情况

需要：

1. 通过部门编号将员工和经理对应起来。
2. 获取员工和经理的当前薪水信息。
3. 比较两者的薪水。

薪水信息都存储在 salaries 这个表中。因此，需要两次查找 salaries 表：

• s1 代表员工的薪水。
• s2 代表经理的薪水。