查询年龄高于各自部门的平均年龄的职工信息,包括职工编号、姓名、性别、学历、所在部门、职级名称和年龄。
使用嵌套子查询的方式查询各自部门的平均年龄作为外层父查询比较的条件。
不会用嘞
SELECT
employee_id,
name,
gender,
education,
department_id,
rank_title,
YEAR(CURDATE()) - YEAR(date_of_birth) AS age
FROM
tbl_employees
WHERE
YEAR(CURDATE()) - YEAR(date_of_birth) > (
SELECT
AVG(YEAR(CURDATE()) - YEAR(date_of_birth))
FROM
tbl_employees
WHERE
department_id = tbl_employees.department_id
)