sql关联查询—将一个表的查询结果作为新表进行查询操作
例题:# 各个部门中 最高工资中最低的那个部门的 最低工资是多少?先考虑取得各个部门最高工资SELECT MAX(salary) AS max_salary,e.`department_id`didFROM employees eGROUP BY e.`department_id`结果为:再将此表命名tmp在进行查询SELECT MIN(max_salary),didFROM(SELECT MA
·
例题:# 各个部门中 最高工资中最低的那个部门的 最低工资是多少?
先考虑取得各个部门最高工资
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
不理解数据库的基础就会犯这种低级错误哈哈
更多推荐
已为社区贡献5条内容
所有评论(0)