mysql子查询高级经典例题
查询部门平均工资是最低部门的信息
法1:先找到平均工资和最低平均工资相等的部门编号,然后再作为筛选条件匹配部门表
从网站建设到定制行业解决方案,为提供成都网站建设、成都网站制作服务体系,各种行业企业客户提供网站建设解决方案,助力业务快速发展。创新互联建站将不断加快创新步伐,提供优质的建站服务。
SELECT d.*
FROM departments d
WHERE d.department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) =
(
SELECT MIN(a)
FROM(
SELECT AVG(salary) a,department_id
FROM employees
GROUP BY department_id
)b
)
)
或
法2:通过排序然后LIMIT直接找到工资最低的部门标号,然后匹配部门表
SELECT d.*
FROM departments d
WHERE d.department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1
);
查询平均工资最低的部门信息和该部门的平均工资
法:将department表和带有最低平均工资的表连接起来,再查询
SELECT d.*,a
FROM departments d
INNER JOIN(
SELECT AVG(salary) a,department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1
) b
ON d.department_id=b.department_id;
查询平均工资最高的job信息
SELECT *
FROM jobs
WHERE jobs.`job_id`=(
SELECT job_id
FROM employees e
GROUP BY e.job_id
ORDER BY AVG(salary) DESC
LIMIT 1
);
查询平均工资高于公司平均工资的部门有些
法:查找平均工资高于公司平均工资的表,然后和部门表连接
SELECT department_name
FROM departments d
INNER JOIN(
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM employees
)
) a
WHERE d.department_id=a.department_id;
查询出公司中所有manager的详细信息在employees表中
SELECT *
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
);
查询各个部门中最高工资中的那个部门的最低工资是多少
SELECT MIN(e.salary)
FROM employees e
WHERE e.department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) DESC
LIMIT 1
)
查询平均工资最高的部门的manager的详细信息:last_name,department_id,email,salary
SELECT e.last_name,e.department_id,e.email,e.salary
FROM employees e
INNER JOIN departments d
ON d.manager_id=e.employee_id
WHERE d.department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
)
当前题目:mysql子查询高级经典例题
链接分享:http://myzitong.com/article/gjjscj.html