mysql 子查询高级经典例题

发布时间:2020-02-29 09:07:27 作者:wx5d21d5e6e5ab1
来源:网络 阅读:309

查询部门平均工资是最低部门的信息
法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
)
推荐阅读:
  1. mysql子查询指的是什么
  2. 什么是mysql子查询及如何运用

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql

上一篇:遨游TypeScript海洋之定义变量和数据类型

下一篇:把面试官唬住了,就要50K。没唬住,就要5K!Android

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》