Oracle_sql练习题4

1. **查找每个部门中工资排名前三的员工信息。**

SELECT department_id, employee_id, employee_name, salary
FROM (
    SELECT 
        department_id, 
        employee_id, 
        employee_name, 
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
    FROM employees
) ranked_employees
WHERE salary_rank <= 3;

**解析：** 这个查询使用了窗口函数 `ROW_NUMBER()` 来对每个部门的员工按工资降序排列，并分配一个排名。外部查询选择工资排名前三的员工信息。

2. **查找每个部门中工资最高的员工信息，以及其所属部门的平均工资。**

SELECT 
    e.department_id, 
    e.employee_id, 
    e.employee_name, 
    e.salary,
    d.avg_department_salary
FROM employees e
INNER JOIN (
    SELECT department_id, AVG(salary) AS avg_department_salary
    FROM employees
    GROUP BY department_id
) d ON e.department_id = d.department_id
WHERE (e.department_id, e.salary) IN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
);

**解析：** 此查询使用了一个内连接来连接员工表和每个部门的平均工资表，然后在外部查询中选择每个部门中工资最高的员工信息，并且获取他们所在部门的平均工资。

3. **查找至少在所有项目中有一次分配记录的员工。**

SELECT employee_id
FROM (
    SELECT DISTINCT employee_id, COUNT(DISTINCT project_id) AS num_projects_assigned
    FROM project_assignments
    GROUP BY employee_id
) t
WHERE num_projects_assigned = (SELECT COUNT(DISTINCT project_id) FROM projects);

**解析：** 此查询首先计算每个员工分配到的不同项目数量，然后外部查询选择分配到所有项目的员工。

4. **查找在 2024 年有工资记录但在 2023 年没有的员工。**

SELECT DISTINCT es.employee_id
FROM employee_salaries es
LEFT JOIN employee_salaries es_2023 ON es.employee_id = es_2023.employee_id
WHERE EXTRACT(YEAR FROM es.month) = 2024
AND EXTRACT(YEAR FROM es_2023.month) != 2023
AND es_2023.employee_id IS NULL;

**解析：** 这个查询使用了自连接和左连接来比较 2024 年和 2023 年的工资记录，并选择在 2024 年有工资记录但在 2023 年没有的员工。

5. **查找每个部门中工资高于该部门平均工资的员工数量。**

SELECT 
    e.department_id,
    COUNT(*) AS num_employees_above_avg_salary
FROM employees e
INNER JOIN (
    SELECT department_id, AVG(salary) AS avg_department_salary
    FROM employees
    GROUP BY department_id
) d ON e.department_id = d.department_id
WHERE e.salary > d.avg_department_salary
GROUP BY e.department_id;

**解析：** 这个查询使用了一个内连接来连接员工表和每个部门的平均工资表，然后筛选出工资高于部门平均工资的员工，并统计每个部门中符合条件的员工数量。

6. **查找没有分配到任何项目的员工。**

SELECT employee_id
FROM employees
WHERE employee_id NOT IN (SELECT DISTINCT employee_id FROM project_assignments);

**解析：** 这个查询使用了子查询来获取所有分配到项目的员工，然后在外部查询中选择没有在子查询结果中出现的员工。

7. **查找工资连续三个月下降的员工。**

SELECT DISTINCT es1.employee_id
FROM employee_salaries es1
JOIN employee_salaries es2 ON es1.employee_id = es2.employee_id
JOIN employee_salaries es3 ON es1.employee_id = es3.employee_id
WHERE es1.month = ADD_MONTHS(es2.month, -1)
AND es2.month = ADD_MONTHS(es3.month, -1)
AND es1.salary > es2.salary
AND es2.salary > es3.salary;

**解析：** 此查询使用了自连接来比较员工连续三个月的工资，如果工资在这三个月内连续下降，则选择这个员工。

8. **查找在所有项目中都工作过的员工。**

SELECT employee_id
FROM (
    SELECT employee_id, COUNT(DISTINCT project_id) AS num_projects_assigned
    FROM project_assignments
    GROUP BY employee_id
) t
WHERE num_projects_assigned = (SELECT COUNT(DISTINCT project_id) FROM projects);

**解析：** 这个查询与之前查询类似，但是它要求员工参与所有项目，而不仅仅是至少一个项目。

9. **查找每个员工在其加入公司后的第一个完整月度周期内的工资总额。**

SELECT 
    employee_id,
    TO_CHAR(MIN(month, 'YYYY-MM'), 'YYYY-MM') AS first_full_month,
    SUM(salary) AS total_salary
FROM employee_salaries
GROUP BY employee_id, TO_CHAR(month, 'YYYY-MM');

**解析：** 此查询使用了 `TO_CHAR` 函数将日期转换为年份和月份，并找到每个员工加入公司后的第一个完整月度周期，然后计算该月度周期内的工资总额。

10. **查找连续 3 个月工资都高于公司平均工资的员工。**

SELECT DISTINCT employee_id
FROM employee_salaries es1
JOIN employee_salaries es2 ON es1.employee_id = es2.employee_id
JOIN employee_salaries es3 ON es1.employee_id = es3.employee_id
JOIN (
    SELECT AVG(salary) AS avg_salary FROM employee_salaries
) avg_sal ON es1.salary > avg_sal.avg_salary AND es2.salary > avg_sal.avg_salary AND es3.salary > avg_sal.avg_salary
WHERE es1.month = ADD_MONTHS(es2.month, -1)
AND es2.month = ADD_MONTHS(es3.month, -2);

**解析：** 这个查询通过自连接比较员工连续三个月的工资是否都高于公司平均工资，并且每个工资月份相隔一个月，如果是，则选择这个员工。