sql关联查询—将一个表的查询结果作为新表进行查询操作

最新推荐文章于 2025-01-08 11:21:59 发布

转载最新推荐文章于 2025-01-08 11:21:59 发布 · 5.3k 阅读

6 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Noctis/p/10497718.html

文章标签：

#数据库

本文通过例题介绍如何进行SQL关联查询，解决如何查询各个部门最高工资中最低的工资问题。首先获取每个部门的最高工资，然后使用子查询或关联查询方法找到这些最高工资中的最低值。不熟悉数据库基础可能会导致此类常见错误。

例题：# 各个部门中最高工资中最低的那个部门的最低工资是多少?

先考虑取得各个部门最高工资

SELECT MAX(salary) AS max_salary,e.`department_id`  did
        FROM employees e
        GROUP BY e.`department_id`

结果为：

再将此表命名tmp在进行查询

SELECT MIN(max_salary),did
FROM
(SELECT MAX(salary) AS max_salary,e.`department_id`  did
        FROM employees e
        GROUP BY e.`department_id`)AS tmp

此时结果却为：

由此可见查询之后的表结构数据之间并无关系

因此应该使用其中的单列数据

解法一：

ELECT MIN(e2.salary)
FROM employees e2
WHERE e2.department_id =
    (SELECT e1.department_id
    FROM employees e1
    GROUP BY e1.department_id
    HAVING MAX(e1.salary) =
        (SELECT MIN(tmp.max_salary) AS min_salary
        FROM(
            SELECT MAX(salary) AS max_salary,e.`department_id`  did
            FROM employees e
            GROUP BY e.`department_id`
            )AS tmp
    )
)

或者将此表与主表进行关联查询

解法二：

SELECT e1.salary
FROM employees e1,
(SELECT MAX(salary) AS max_salary,e.`department_id`  did
        FROM employees e
        GROUP BY e.`department_id`
        ORDER BY max_salary
        LIMIT 0,1
)AS temp
WHERE e1.department_id = temp.did

不理解数据库的基础就会犯这种低级错误哈哈

转载于:https://www.cnblogs.com/Noctis/p/10497718.html