美文网首页常见面试
mysql子查询经典案例

mysql子查询经典案例

作者: majorty | 来源:发表于2019-03-24 20:56 被阅读0次

1. 查询工资最低的员工信息: last_name, salary

①查询最低的工资

SELECT MIN(salary)
FROM employees

②查询last_name,salary,要求salary=①

SELECT last_name,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);

2. 查询平均工资最低的部门信息

方式一:

①各部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

②查询①结果上的最低平均工资

SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep

③查询哪个部门的平均工资=②

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep

);

④查询部门信息

SELECT d.*
FROM departments d
WHERE d.department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(ag)
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep

)

);

方式二:

①各部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

②求出最低平均工资的部门编号

SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;

③查询部门信息

SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
);

3. 查询平均工资最低的部门信息和该部门的平均工资

①各部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

②求出最低平均工资的部门编号

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1;

③查询部门信息

SELECT d.*,ag
FROM departments d
JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1

) ag_dep
ON d.department_id=ag_dep.department_id;

4. 查询平均工资最高的 job 信息

①查询最高的job的平均工资

SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1

②查询job信息

SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1

);

5. 查询平均工资高于公司平均工资的部门有哪些?

①查询平均工资

SELECT AVG(salary)
FROM employees

②查询每个部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

③筛选②结果集,满足平均工资>①

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM employees

);

6. 查询出公司中所有 manager 的详细信息.

①查询所有manager的员工编号

SELECT DISTINCT manager_id
FROM employees

②查询详细信息,满足employee_id=①

SELECT *
FROM employees
WHERE employee_id =ANY(
SELECT DISTINCT manager_id
FROM employees

);

7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少

①查询各部门的最高工资中最低的部门编号

SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1

②查询①结果的那个部门的最低工资

SELECT MIN(salary) ,department_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1

);

8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

①查询平均工资最高的部门编号

SELECT
department_id
FROM
employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1

②将employees和departments连接查询,筛选条件是①

SELECT 
    last_name, d.department_id, email, 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子查询经典案例

    1. 查询工资最低的员工信息: last_name, salary ①查询最低的工资 SELECT MIN(sal...

  • mysql子查询案例

    查询和Zlotkey相同部门的员工姓名和工资 查询工资比平均工资高的员工的员工号、姓名和工资 查询各部门中工资比本...

  • 子查询经典案例

    1. 查询工资最低的员工信息:last_name,salary SELECT last_name,salaryFR...

  • MySQL 子查询、内联结、外联结

    子查询MySQL 子查询版本要求:MySQL4.1引入了对子查询的支持。子查询:嵌套在其他查询语句中的查询。 示例...

  • mysql 查询

    mysql的查询、子查询及连接查询 一、mysql查询的五种子句 where(条件查询)、having(筛选)、g...

  • 【MySQL】MySQL查询——子查询

    查出本网站,最新的(goods_id最大)的商品select goods_id,goods_name,cat_id...

  • 第六章 查询性能优化(下)

    MySQL查询优化器的局限性 关联子查询 MySQL的关联子查询实现的很差,最好改成左外连接(LEFT OUTER...

  • 查询性能优化

    MySQL查询优化器的局限性 关联子查询 MySQL的子查询实现的非常糟糕,最糟糕的一类查询是where条件中包含...

  • MySql查询-子查询

    子查询 在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为...

  • MySQL--基础二

    本节总结MySQL的筛选条件,聚合与分组,子查询,连接查询。 MySQL的筛选条件 MySQL中的比较运算符: 比...

网友评论

    本文标题:mysql子查询经典案例

    本文链接:https://www.haomeiwen.com/subject/kvjpvqtx.html