导读：
MySQL是一种常用的关系型数据库管理系统，支持多种查询方式 。本文将介绍复杂子查询的使用方法，并提供示例 。
1. 查询每个部门的平均工资和最高工资
SELECT department, AVG(salary), MAX(salary)
FROM employee
GROUP BY department;
2. 查询每个部门的员工数目和平均工资
【mysql复杂函数 mysql复杂子查询示例】SELECT department, COUNT(*), AVG(salary)
3. 查询有至少两名员工的部门
SELECT department
GROUP BY department
HAVING COUNT(*) >= 2;
4. 查询每个部门中薪水排名前三的员工
SELECT department, name, salary
FROM (SELECT department, name, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employee) AS tmp
WHERE rank <= 3;
5. 查询每个部门中工作年限超过平均值的员工
SELECT department, name, years_of_service
FROM (SELECT department, name, years_of_service,
AVG(years_of_service) OVER (PARTITION BY department) AS avg_years
WHERE years_of_service > avg_years;
总结：
复杂子查询可以帮助我们更加灵活地查询数据，满足不同的需求 。在实际应用中，根据具体情况选择合适的查询方式，可以提高查询效率和准确性 。
