查询高于20号部门最低工资的员工的编号和姓名及工资。
时间: 2024-10-08 22:16:44 浏览: 118
第09章_子查询1
要查询高于20号部门最低工资的员工信息,首先需要确定每个部门的最低工资。假设有一个`departments`表用于存储部门信息,包括`department_id`和`min_salary`(部门最低工资),以及`employees`表存储员工信息,包括`employee_id`, `name`, 和 `salary`,同时有一个`department_employee`表连接了这两个表。查询可以这样编写:
```sql
WITH department_wages AS (
SELECT d.department_id, MIN(e.salary) as min_salary_per_dept
FROM departments d
JOIN department_employee de ON d.department_id = de.department_id
GROUP BY d.department_id
)
SELECT e.employee_id, e.name, e.salary
FROM employees e
JOIN department_employee de ON e.employee_id = de.employee_id
JOIN department_wages dw ON e.salary > dw.min_salary_per_dept AND de.department_id = dw.department_id
WHERE dw.department_id = 20;
```
这个查询先通过子查询`department_wages`找出每个部门的最低工资,然后在主查询中筛选出20号部门并且工资高于该最低工资的所有员工。
阅读全文